• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Countdown Formula.

Certificates!

New Member
I have a formula which works - with the aid of a "Helper Column" which is currently hidden.

The formula I use is: IF(TODAY()>=Q6,Q3-Q6,MIN(365,IF(Q4<=Q3,Q3-Q4,Q3-Q6)))

Q5 = Today.

What this does is count the number of days to the year end (30 Sept 2022) yet stopping at the end of each month. Thus if one were to look at the July monthly worksheet today it would say 61 days in P5

The August sheet will stop counting down after 31st August and say 30 days in P5

I could really do with unhiding column Q for other things in the worksheets. Is there a better way to do what I want to do?
 

Attachments

bosco_yip

Excel Ninja
Try, just guessing............

Days Left in Year

In P5, enter formula :

=IF(Q4>=Q3,0,MAX(DATEDIF(Q4,Q3,"d"),30))
 
Last edited:

Certificates!

New Member
Thanks for the reply.

I'll give it a shot when I'm back home. later on this evening. I don't have access to the excel file right now.
 

Certificates!

New Member
This works fine it two out of three of the situations. It does not work though, for the months in the past.

When I key this formula into the worksheet for the month ending July 2022, it still says that there are 49 days left in my calendar year, when in fact the answer should be 61. The 31 days in August and the 30 in Spetember. It also gave 49 when I tried it in February instead fo 214.

Thanks very much for your efforts though, it's always appreciated.
 
Top