formula for calculating cycles

[abound1]

I need some help with a simple calculation - calculating the number of 'cycles' btwn 2 dates – ie. the number of 14-day cycles plus the number of days transpired (into the next incompleted cycle).

As dates are entered into Col E, the number of cycles (plus days transpired) will show up in Col F as “cycle# / days”.

Example:
(today’s date :11/20/05)
date entered in Col E: 10/01/05
result in Col F: 3/9
3 (14-day) cycles + 9 days transpired

I tried “

=DATEDIF(E2,NOW(),"d")/14

”, but it returns a decimal value of the cycles rather than the cycle PLUS the actual number of transpired days.

Thanks in advance for your help

 

[fairwinds]

Hi,

Try:

=INT((TODAY()-E2)/14)&"/"&MOD(TODAY()-E2,14)

or maybe you need:

=INT((TODAY()-E2+1)/14)&"/"&MOD(TODAY()-E2+1,14)

 

[jslomax]

try this

=INT(DATEDIF(E2,NOW(),"d")/14)&"/"&MOD(DATEDIF(E2,NOW(),"d"),14)

it seems to work with modulo giving the remainder of your cycle.

John

 

[abound1]

Thanks a lot Fairwinds and John!

Fairwinds, your second formula works perfectly:

=INT((TODAY()-E2+1)/23)&"/"&MOD(TODAY()-E2+1,23)

the others seemed to come up short by 1 day.

Thanks again for your help, much appreciated