# formula for calculating cycles

#### abound1

##### Board Regular
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 “
Code:
``=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.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi,

Try:

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

or maybe you need:

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

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

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

Replies
1
Views
289
Replies
1
Views
418
Replies
4
Views
212
Replies
1
Views
196
Replies
13
Views
1K

1,203,502
Messages
6,055,769
Members
444,822
Latest member
Hombre

### 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.

### Which adblocker are you using?

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

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