Help with dates and cycles

**michael_hazo** · 06-07-2013, 08:06 AM

I have a two week cycle that excludes weekends. This cycle repeats over and over. I know the first date of the cycle is May 6. I need a table that will allow me to look up todays date and return which day of the cycle the date belongs to. How would I do this?

e.g. the days of the cycle could be numbered as such:

Monday (week A) = Day 1
Tuesday (week A) = Day 2
.
.
.
.
Friday (Week B) = Day 10

So if Day 1 of the cycle is May 6 and todays date is June 7. That means that today is Day 5 of the cycle.

How could I setup a table in Excel that will work this out for me?

Many thanks to all the excel pros that will no doubt help me out :-)

Michael

**zumbalj** · 06-07-2013, 08:23 AM

I would use something like =ROUNDDOWN((TODAY()-41400)/14,1) (I assumed you meant May 6, 2013. If not, you'll need to change the 41400 to whatever the date value for the day you want is).

That should give you the cycle number. If you subtract out the number before the decimal, and the result is <.5, it's in week 1 and >.5 it's in week 2. You could figure out what fraction equals each day and put that in a table with the day number and use a VLOOKUP to assign the day.

**daddylonglegs** · 06-07-2013, 08:29 AM

This formula will give you the day number

=MOD(TODAY()-DATE(2013,5,6),14)+1

format as general

To test that replace TODAY() in the formula with a cell reference then you can test various dates by putting them in that cell

**zumbalj** · 06-07-2013, 10:41 AM

Good idea! I think our formulas are doing basically the same thing, just in a little different way. Yours is definitely more concise. MOD is not a function I've used before, I will have to keep it in mind, thanks!