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

  • KPH
    KPH ✭✭✭✭✭✭
    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

  • KPH
    KPH ✭✭✭✭✭✭
    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.

  • Lia J
    Lia J ✭✭
    Options

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

  • KPH
    KPH ✭✭✭✭✭✭
    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.

  • Lia J
    Lia J ✭✭
    Options

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

  • KPH
    KPH ✭✭✭✭✭✭
    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.

  • Lia J
    Lia J ✭✭
    Options

    awesome you are genius. thank you so much.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    You are welcome! that was fun!

  • Lia J
    Lia J ✭✭
    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.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    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
    Lia J ✭✭
    Options

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

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Can you copy the formula from your sheet into here?

  • Lia J
    Lia J ✭✭
    Options

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

  • KPH
    KPH ✭✭✭✭✭✭
    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


  • Lia J
    Lia J ✭✭
    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.

  • KPH
    KPH ✭✭✭✭✭✭
    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!