1

Im hoping someone can help or advise.

I want a formula to count the number of days left in a month from the date of the first of that month. If that month has passed, I would like to it to stop at zero.

For ease, my image shows a yellow column, they are the answers.

When I wrote this, the date was 23rd july 2023, therefore in july there would have been be 9 days left in the month on that day.

Please help - thank you. Date formula needed

Improve this question
3
  • 2
    What have you tried so far? Where are you stuck?
    – Markus Meyer
    Commented Jul 24, 2023 at 7:25
  • Hi Markus, To be hones 0 Im completely stumped on this one. I thought perhaps of doing an IF formula counting the difference from todays date and if the result was negative to stop at 0 but the outcomes were limitless with future dates.
    – Chris
    Commented Jul 24, 2023 at 7:28
  • I tried this which puts all zeros in, but doesn't work for Jul onwards - =IF(H2="","",MAX(0,(-TODAY())))
    – Chris
    Commented Jul 24, 2023 at 7:33

2 Answers 2

Reset to default
3

Try the following formula:

=IF(MONTH(A7)=MONTH(TODAY()),B7-DAY(TODAY()),IF(MONTH(A7)>MONTH(TODAY()),B7,0))

It compares the current month with the month on column A. If there are the same, it calculates the number of days left. If not, it compares if the month in column A is greater than the current month. If yes, it display the number of days from column B. If the month is lower than the current month, 0 is shown.

Drag it up and down to fill the other cells. Adapt the formula to fit your range(s).

enter image description here

Demo

enter image description here

Improve this answer
0

=IF(TODAY()>EOMONTH(H2,0),0,IF(EOMONTH(TODAY(),0)<EOMONTH(H2,0),DAY(EOMONTH(H2,0)),IF(TODAY()>H2,DAY(EOMONTH(H2,0))-(DAY(TODAY())-1),DAY(EOMONTH(H2,0))-(DAY(H2)-1))))

Improve this answer
1
  • 2
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jul 24, 2023 at 10:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .