Testing schedule based on frequency and date

Juddman79

Juddman79

New Member
Joined
Feb 17, 2020
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
Evening all,

I don't know if what I'm asking is possible but if it is I'm hoping somebody is able to help me. To be honest I have no idea where to start as my knowledge of coding excel is minimal.

Is it possible to create a schedule of test dates based on a pre-set value in a cell and a start date? I would like the row to auto populate based on the the date of the previous test completion date and the pre defined frequency (column G) for the specific test (column F). J5 is automatically filled based on the date in cell I6 and the frequency list in cell G5 (this would need to automatically change if the test type changes)and so on along the row for the ten tests. I've attached a screenshot and also a link to the sheet.

1701375501343.png



Thanks in advance.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Does something like this work?:

Or do you need to schedule the next test on working days from monday to friday?

Schedule.xlsx
DEFGHIJKLMNOP
3Test TypeFrequency [Days]Test 1Test 2Test 3Test 4Test 5Test 6Test 7Test 8Test 9Test 10
4Test A3Scheduled01/01/202405/01/202408/01/202412/01/202416/01/202421/01/202424/01/202427/01/202431/01/202403/02/2024
5Completed02/01/202405/01/202409/01/202413/01/202418/01/202421/01/202424/01/202428/01/202431/01/2024
6Result
7Test B5Scheduled01/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/202416/02/2024
8Completed02/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/2024
9Result
10Test D14Scheduled01/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/202407/05/2024
11Completed02/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/2024
12Result
13Test E30Scheduled01/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/202428/09/2024
14Completed02/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/2024
15Result
16
17
18Test TypeFrequency
19Test A3
20Test B5
21Test C10
22Test D14
23Test E30
Hoja1
Cell Formulas
RangeFormula
E4,E13,E10,E7E4=INDEX(TestFrequency,MATCH(D4,TestTypes,0))
H4:P4,H13:P13,H10:P10,H7:P7H4=IF(G5>0,G5+$E4,"")
Named Ranges
NameRefers ToCells
TestFrequency=Tests[Frequency]E13, E10, E7, E4
TestTypes=Tests[Test Type]E13, E10, E7, E4
Cells with Data Validation
CellAllowCriteria
D4List=TestTypes
D7List=TestTypes
D10List=TestTypes
D13List=TestTypes
 
Upvote 1
Solution
felixstraube said:
Does something like this work?:

Or do you need to schedule the next test on working days from monday to friday?

Schedule.xlsx
DEFGHIJKLMNOP
3Test TypeFrequency [Days]Test 1Test 2Test 3Test 4Test 5Test 6Test 7Test 8Test 9Test 10
4Test A3Scheduled01/01/202405/01/202408/01/202412/01/202416/01/202421/01/202424/01/202427/01/202431/01/202403/02/2024
5Completed02/01/202405/01/202409/01/202413/01/202418/01/202421/01/202424/01/202428/01/202431/01/2024
6Result
7Test B5Scheduled01/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/202416/02/2024
8Completed02/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/2024
9Result
10Test D14Scheduled01/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/202407/05/2024
11Completed02/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/2024
12Result
13Test E30Scheduled01/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/202428/09/2024
14Completed02/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/2024
15Result
16
17
18Test TypeFrequency
19Test A3
20Test B5
21Test C10
22Test D14
23Test E30
Hoja1
Cell Formulas
RangeFormula
E4,E13,E10,E7E4=INDEX(TestFrequency,MATCH(D4,TestTypes,0))
H4:P4,H13:P13,H10:P10,H7:P7H4=IF(G5>0,G5+$E4,"")
Named Ranges
NameRefers ToCells
TestFrequency=Tests[Frequency]E13, E10, E7, E4
TestTypes=Tests[Test Type]E13, E10, E7, E4
Cells with Data Validation
CellAllowCriteria
D4List=TestTypes
D7List=TestTypes
D10List=TestTypes
D13List=TestTypes
Click to expand...

Thanks for your response. A test can happen any day of the week, so no it doesn't need to be specifically a working day.

I did see an email about changing the sharing permissions of the file which I have done, but I don't know if you got a message saying this.

It does look like this will work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Populate a cell with text and formatting based on a whether a date field falls within a date range
Replies
3
Views
143
Micron
M
Burrgogi
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
906
Burrgogi
Burrgogi
M
  • Question Question
Populate Fee Schedule Based on Dates
Replies
5
Views
299
Cubist
Cubist
M
  • Question Question
Macro/VBA to add text from another cell into current cell when I type a certain value.
Replies
9
Views
983
mrmcmillanrevis
M
M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
263
mike4777
M

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top