Count down in Weeks

[NikkuSan7]

Good morning hive mind!

Excel date functions tend to throw me for a loop and I'm sure I'm overlooking something. I'm looking to create a countdown timer. You have your start date, you have your end date, and in a third cell the timer counts down the weeks remaining to your end goal starting from today:

A1: 8/20/2020 (Start Date)
A2: 2/4/2021 (End Date)
A3: ***Weeks remaining from Today***

Formula presently in A3: =TODAY()-(DAYS(EndDate,StartDate)/7)

As I have the formula in A3 presently written, excel returns a result of 44116. I thought flipping the formula to =(DAYS(EndDate,StartDate)/7)-TODAY(), This simply gives the same answer as a negative number.

I assume my current result in A3 is due to how excel recognizes dates as numbers counting up from 1900, as well as a weird interaction between the TODAY() and DAYS functions. I have a feeling that this isn't the right formula at all for this type of operation.

Thanks for any help!

[Pete_UK]

The TODAY() function will currently return 44140, which is the number of days since 1st January 1900. You are subtracting from it the number of weeks between the start date and the end date, which will be 24 in your example. I think you need to subtract TODAY() from the end date, and then divide it by 7 to turn it into weeks - probably better to use INT, rather than get fractional weeks.

Hope this helps.

Pete

[ChemistB]

It should be in A3
=IF(TODAY()<A2, 0, (TODAY()-A2)/7)
Assuming you want 0 if the end date has passed already. Be sure to format the cell as General or Number.
Does that work for you?

[Pete_UK]

Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

[NikkuSan7]

It should be in A3
=IF(TODAY()<A2, 0, (TODAY()-A2)/7)
Assuming you want 0 if the end date has passed already. Be sure to format the cell as General or Number.
Does that work for you?

Yup! That got me to where I needed to go! Thanks!

[NikkuSan7]

The TODAY() function will currently return 44140, which is the number of days since 1st January 1900. You are subtracting from it the number of weeks between the start date and the end date, which will be 24 in your example. I think you need to subtract TODAY() from the end date, and then divide it by 7 to turn it into weeks - probably better to use INT, rather than get fractional weeks.

Hope this helps.

Pete

I will have to experiment with INT. That’s a function I have needed. Yet.