How to calculate how many days left for the month?

Lia J

what formula to use that will automatically calculate how many days are left till the end of the month? E.G. till the end of November months there is left 12 days. so mo column can show those remaning 12 days

KPH

You can build a date using the DATE function combined with a couple of others like this:

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)

This creates a date where

• the year is today's year
• the month is today's month plus one (ie next month)
• the day is the 1st

So this gives you the first of next month. If we minus 1 from that date we get the last day of the current month. It is a bit of a circle as different months have different end dates (28th, 30th, or 31st) but they all have the same start so we can use that.

So this formula gives us the last day of the current month.

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1

If you use that to replace the fixed date the 1st formula (=Date@row - TODAY(), which calculates the number of days between now and a fixed date) you can calculate the days between now and the last day of the current month (with one exception).

=(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1)- TODAY()

This will not work in December.

Next step

To make this work in December we need a slightly different formula to find the start of the next month.

This formula creates a date where

• the year is today's year plus one
• the month is the first month of the year (January)
• the day is the 1st

=(DATE((YEAR(TODAY()) + 1), 1, 1))

If we minus 1 day from that date we get the last day of December in the current year.

=(DATE((YEAR(TODAY()) + 1), 1, 1)) - 1

So we can combine this with the formula above that works for all other months, like this:

=IF(MONTH(TODAY()) = 12, (DATE((YEAR(TODAY()) + 1), 1, 1)) - 1, (DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1) - TODAY())

The first part up to the bold section is looking at the month today and deciding if it is December. The bold part is calculating the date if we are in December the part after it is figuring out the last day of the current month, if we are not in December.

KPH

To find the number of days between now and a fixed date you can use

=Date@row - TODAY()

where Date@row contains the fixed date.

So, if Date is 11/30/23 then the result today (Nov 18th) is 12.

But it sounds like you want to go one step further and automatically figure out the end of the current month as well? Is that correct? I do not believe smartsheet has a function for month end yet, so we'd need to figure out a formula with the functions that are available.

Lia J

thats correct I'm looking to figure out the way to calculate days left for each month. I use mm dd yy format.

KPH

You can build a date using the DATE function combined with a couple of others like this:

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1)

This creates a date where

• the year is today's year
• the month is today's month plus one (ie next month)
• the day is the 1st

So this gives you the first of next month. If we minus 1 from that date we get the last day of the current month. It is a bit of a circle as different months have different end dates (28th, 30th, or 31st) but they all have the same start so we can use that.

So this formula gives us the last day of the current month.

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1

If you use that to replace the fixed date the 1st formula (=Date@row - TODAY(), which calculates the number of days between now and a fixed date) you can calculate the days between now and the last day of the current month (with one exception).

=(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1)- TODAY()

This will not work in December.

Next step

To make this work in December we need a slightly different formula to find the start of the next month.

This formula creates a date where

• the year is today's year plus one
• the month is the first month of the year (January)
• the day is the 1st

=(DATE((YEAR(TODAY()) + 1), 1, 1))

If we minus 1 day from that date we get the last day of December in the current year.

=(DATE((YEAR(TODAY()) + 1), 1, 1)) - 1

So we can combine this with the formula above that works for all other months, like this:

=IF(MONTH(TODAY()) = 12, (DATE((YEAR(TODAY()) + 1), 1, 1)) - 1, (DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1) - TODAY())

The first part up to the bold section is looking at the month today and deciding if it is December. The bold part is calculating the date if we are in December the part after it is figuring out the last day of the current month, if we are not in December.

Lia J

it worked thank you so much. it would be great to have it for December month as well.

KPH

Wonderful. While you were testing that, I added a "Next step" to the answer above for December so that anyone coming across this in the future has the full answer in one place.

Lia J

awesome you are genius. thank you so much.

KPH

You are welcome! that was fun!

Lia J

using this formula =IF(MONTH(TODAY()) = 12, (DATE((YEAR(TODAY()) + 1), 1, 1)) - 1, (DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1) - TODAY()) I got error #invalid column value. I guess its still an issue for December month.

KPH

Hi Lia

The formula looks fine. Have you pasted it into a different column today? Maybe one that isn't a date column?

Lia J

no I didn't even move. its in the same column.

KPH

Can you copy the formula from your sheet into here?

Lia J

=IF(MONTH(TODAY()) = 12, (DATE((YEAR(TODAY()) + 1), 1, 1)) - 1, (DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1) - TODAY())

KPH

It works fine for me as long as I paste it into a date column. I can't see any difference between what you have and what I have

Lia J

oh I don't want it to show me the actual date. so what I want to see is number of days left for the month. like if its 12/01/2023 today so days left are 30. for November month thats how the formula been working.

KPH

Ah yes, I remember now. I put all my effort into figuring out how to find out the last day of the current month if we were in December, I forgot the next step. All you need is to add the part that subtracts today from the last date in December.

This should do it.

=IF(MONTH(TODAY()) = 12, ((DATE((YEAR(TODAY()) + 1), 1, 1)) - 1) - TODAY(), (DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1) - TODAY())