Day ## of ## counting

[xxxloganxxx]

I am looking for a formula that will count days in increments of 1 through 40. I need it to look like this: DAY 1 of 40

Whereas only the "1" counts up to 40 every 24 hours. And maybe a button to reset the counter back to "1" I appreciate any help at all in advance.

[JBeaucaire]

Select a cell to enter the kickoff date. Let's make it AA1 for now.

In your tracking cell, enter the following formula:

=TODAY()-$AA$1+1

Now Ctrl+1 to open the Format Box, select Category: Custom and enter this custom format:

0" of 40"

Last step is a macro to reset the date in AA1.

Sub Reset()
    Range("AA1").Value = Date
End Sub

Put that on a button, or just run the macro manually.

[JBeaucaire]

Also, if you want the time to expire, you could use this formula:

=IF(TODAY()-H7+1>40,"Time's Up!",TODAY()-H7+1)

[xxxloganxxx]

Thanks a bunch! Exactly what I needed. Cheers!

[xxxloganxxx]

Actually I have one more question, I need to use this formula on a spreadsheet I am using now but the start date of the 1 of 40 days is in the past. How would I adjust it so that the current day (which is day 25) will be reflected? The actual start date of this count is 15 Dec 2008.

Thanks Again!

[xxxloganxxx]

Disregard, I have it all sorted now, Just had to modify a little. Thanks again.......case closed

[JBeaucaire]

In the cell you're using the macro to update, manually put in the starting date you want, in the same cell, so it keeps working as designed.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]