# How to calculate how many days left for the month?

Options
✭✭

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

## Best Answer

• ✭✭✭✭✭✭
edited 11/18/23 Answer ✓
Options

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.

«1

## Answers

• ✭✭✭✭✭✭
edited 11/18/23
Options

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
edited 11/18/23 Answer ✓
Options

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.

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

awesome you are genius. thank you so much.

• ✭✭✭✭✭✭
Options

You are welcome! that was fun!

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Hi Lia

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

Can you copy the formula from your sheet into here?

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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())

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!