Rotating Date

Courtney Boehm

I have a car park roster and looking to populate the cell each fortnight with a rotating date from a starting date. I have it somewhat working with the following formula but i have to keep changing the end number (Multiplier).

Do anyone have an alternative formula for a Monday week fortnightly rotating date?

Brian_Richardson

I'm so sorry, I whipped that off top of my head and didn't check it. You're right, that formula would only give you a date on alternating Mondays, but then be blank.

Instead try the formula below. I checked this time :-)

This formula assumes you have a column called "Extension" where you put, for each person, the number of days out that you'd like to have their date scheduled. So Julie could be 14 days out from each fortnight, Gay to be 28 days out from each fortnight, etc. The Extension days don't have to be multiples of 7, BTW, can be anything. If you need Julie to be the Wed following each fortnight Monday, you'd put 2 in Extension.

The formula works like this:

• Calculate the difference between the current week number and the last even week number. Then multiply by 7 to give number of days if there's a delta.
• Calculate the number of days between the current day and Monday and add it
• Take the total from above and make it negative.
• Essentially what you've done at this step is gotten the number of days since the last even week's Monday
• Subtract that delta from the Extension number.
• This results in a date that is the number of Extension days since the last even week's Monday.

So as a practical example, today, July 17, is day 4 (Wed) of week 29. I have an Extension amount of 14 days defined for myself. So I want the date that represents 14 days from July 8, the last even week's Monday.

• Week 29 - Week 28 (last even week number) = 1. 1X7 = 7 days.
• Day 4 -2 = 2 days
• -(7 days + 2 days) = -9 days
• -9 days + 14 days (extension) = 5 days from July 17
• Result is July 22

=TODAY(-((WEEKNUMBER(TODAY()) - FLOOR(WEEKNUMBER(TODAY()), 2)) * 7 + (WEEKDAY(TODAY()) - 2)) + Extension@row)

Brian_Richardson

It looks like you want a different delta for each person? So you

=IF(AND(ISEVEN(WEEKNUMBER(Today())),WEEKDAY(Today())=2),Today(14))

This will give you the dates, changing only every other Monday, and will add the bolded number of days to Today. So if you want Julie to be out 2 weeks, use 14. If you want Gray to be out 4 weeks, use 28, etc.

Or you could add a column called "Extension" or something, put 14,28,10,whatever forward date delta you want for each person, and replace the "14" in the column above with Extension@row. Then you can easily control how many days out you want each person to be scheduled for, from the alternating Monday.

=IF(AND(ISEVEN(WEEKNUMBER(Today())),WEEKDAY(Today())=2),Today(Extension@row))

Courtney Boehm

Hi @Brian_Richardson. I input both those formulas into my workbook and got blank dates appear? have i missed something aka inputting a start date or something into the formula?

Brian_Richardson

I'm so sorry, I whipped that off top of my head and didn't check it. You're right, that formula would only give you a date on alternating Mondays, but then be blank.

Instead try the formula below. I checked this time :-)

This formula assumes you have a column called "Extension" where you put, for each person, the number of days out that you'd like to have their date scheduled. So Julie could be 14 days out from each fortnight, Gay to be 28 days out from each fortnight, etc. The Extension days don't have to be multiples of 7, BTW, can be anything. If you need Julie to be the Wed following each fortnight Monday, you'd put 2 in Extension.

The formula works like this:

• Calculate the difference between the current week number and the last even week number. Then multiply by 7 to give number of days if there's a delta.
• Calculate the number of days between the current day and Monday and add it
• Take the total from above and make it negative.
• Essentially what you've done at this step is gotten the number of days since the last even week's Monday
• Subtract that delta from the Extension number.
• This results in a date that is the number of Extension days since the last even week's Monday.

So as a practical example, today, July 17, is day 4 (Wed) of week 29. I have an Extension amount of 14 days defined for myself. So I want the date that represents 14 days from July 8, the last even week's Monday.

• Week 29 - Week 28 (last even week number) = 1. 1X7 = 7 days.
• Day 4 -2 = 2 days
• -(7 days + 2 days) = -9 days
• -9 days + 14 days (extension) = 5 days from July 17
• Result is July 22

=TODAY(-((WEEKNUMBER(TODAY()) - FLOOR(WEEKNUMBER(TODAY()), 2)) * 7 + (WEEKDAY(TODAY()) - 2)) + Extension@row)

Courtney Boehm

Thanks @Brian_Richardson that is perfect and thank you for the detailed explanation.