Formula to report lab test required based on date entry.

[msca17]

Hello, I have 4 different lab tests I run every week for 4 weeks, then it resets. So lab test 1 on week 1, lab test 2 on week 2...then week 5 back to lab test 1. If I know that the week of Jan 7 2019 lab test 3 was run, how do I create a formula that allows me to enter a date and it reports what lab test I need to run that week?

 

[jondavis1987]

This is the formula you will need. You'll need to change G1 to whatever cell has the date in it. This formula assumes you started Test 1 on week one of the year. For instance we are in week 19 of the year. This has week 19 as being the Test 3 week. If this week isn't the test 3 week, you'll need to change the "Test #" accordingly

=IF(WEEKNUM(G1)=3,"Test 3",IF(WEEKNUM(G1)=7,"Test 3",IF(WEEKNUM(G1)=11,"Test 3",IF(WEEKNUM(G1)=15,"Test 3",IF(WEEKNUM(G1)=19,"Test 3",IF(WEEKNUM(G1)=23,"Test 3",IF(WEEKNUM(G1)=27,"Test 3",IF(WEEKNUM(G1)=31,"Test 3",IF(WEEKNUM(G1)=35,"Test 3",IF(WEEKNUM(G1)=39,"Test 3",IF(WEEKNUM(G1)=39,"Test 3",IF(WEEKNUM(G1)=47,"Test 3",IF(WEEKNUM(G1)=51,"Test 3",IF(WEEKNUM(G1)=2,"Test 2",IF(WEEKNUM(G1)=6,"Test 2",IF(WEEKNUM(G1)=10,"Test 2",IF(WEEKNUM(G1)=14,"Test 2",IF(WEEKNUM(G1)=18,"Test 2",IF(WEEKNUM(G1)=22,"Test 2",IF(WEEKNUM(G1)=26,"Test 2",IF(WEEKNUM(G1)=30,"Test 2",IF(WEEKNUM(G1)=34,"Test 2",IF(WEEKNUM(G1)=38,"Test 2",IF(WEEKNUM(G1)=42,"Test 2",IF(WEEKNUM(G1)=46,"Test 2",IF(WEEKNUM(G1)=50,"Test 2",IF(WEEKNUM(G1)=1,"Test 1",IF(WEEKNUM(G1)=5,"Test 1",IF(WEEKNUM(G1)=9,"Test 1",IF(WEEKNUM(G1)=13,"Test 1",IF(WEEKNUM(G1)=17,"Test 1",IF(WEEKNUM(G1)=21,"Test 1",IF(WEEKNUM(G1)=25,"Test 1",IF(WEEKNUM(G1)=29,"Test 1",IF(WEEKNUM(G1)=33,"Test 1",IF(WEEKNUM(G1)=37,"Test 1",IF(WEEKNUM(G1)=41,"Test 1",IF(WEEKNUM(G1)=45,"Test 1",IF(WEEKNUM(G1)=49,"Test 1","Test 4")))))))))))))))))))))))))))))))))))))))

 

[Fluff]

Hi & welcome to MrExcel.
How about
="Test "&IF(MOD(WEEKNUM(G4)+1,4)=0,4,MOD(WEEKNUM(G4)+1,4))

 

[Tetra201]

@ Fluff:

Your formula would stumble upon transitioning from year 2022 to year 2023.

Here is a shorter formula to try:

="Test "&MOD(INT((G4-1)/7),4)+1

 

[Tetra201]

Just in case, here is a more general formula:

=MOD(INT((A2-1)/7)-INT(($A$1-1)/7)+$B$1-1,4)+1

where cell A1 has a date with a known test number (e.g., Jan 7, 2019)
cell B1 has the known test number (e.g., 3)
cell A2 has a date for which test number needs to be calculated.