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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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

Burrgogi
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
877
Burrgogi
Burrgogi
M
  • Question Question
Populate Fee Schedule Based on Dates
Replies
5
Views
286
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
980
mrmcmillanrevis
M
M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
247
mike4777
M
D
  • Solved
Overriding Conditional Formatting Color with Manual Entries of a different color
2
Replies
15
Views
4K
Joe4
Joe4

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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