EOMONTH formula request

I would really like to see the EOMONTH formula. There is a way to do that with the current formulas, but as an example of how long it is, here is what's needed for setting a due date to the end of the current month if there are more than 10 days left in the month or setting it to the following month if there are less than 10 days.

First in Excel: IF((EOMONTH(TODAY(),0)-TODAY())<10,EOMONTH(TODAY(),1),EOMONTH(TODAY(),0)

Now in Smartsheet: IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 1, 1) - 1, DATE(YEAR(TODAY()), (MONTH(TODAY()) + 1), 1) - 1) - TODAY() < 10, IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 2, 1) - 1, DATE(YEAR(TODAY()), (MONTH(TODAY()) + 2), 1) - 1), IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 1, 1) - 1, DATE(YEAR(TODAY()), (MONTH(TODAY()) + 1), 1) - 1))

If anyone has suggestions about shortening the Smartsheet formula, I'm all ears, but I think the answer is an EOMONTH formula.

Find more posts tagged with

Formulas

Comments

J. Craig Williams

Mattisphere,

1. Replace

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

with

DATE(YEAR(TODAY()),12,31)

2. After Dec 21st, your formula is wrong, I think. Doesn't it always return Dec 31st?

3. I copied your formula but got #UNPARSEABLE error.

4. I would move the formula for end-of-this-month

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

and end-of-next-month

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

to their own cells.

This would prevent you from calculating that for every row.

Then your formula would be

=IF(YOUR-EOM - TODAY() > 10, YOUR-EOM, NEXT-EOM)

where

YOUR-EOM & NEXT-EOM are your new cells.

5. As I said, I got #UNPARSEABLE. Still, your formula actually looks TOO SHORT.

Here's the psuedo code for what I think you are trying to do:

IF ( MONTH = DEC )
IF ( EOM12 - TODAY > 10)
    EOM12
ELSE
    NEOM12
ELSE
IF ( EOM - TODAY > 10)
    EOM
ELSE
    NEOM

Note that EOM12 and EOM are calculating the end-of-month, but the equation is slightly different since you know whether you are in December or not.

Same with NEOM12 and NEOM.

Maybe I'm missing something with my cut & paste.

Craig

Mattisphere

Thank you, Craig, for the response. The existing formula works fine and yields the desired result - it looks like I left off a nested IF when pasting. Here is the current formula: =IF(IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 1, 1) - 1, DATE(YEAR(TODAY()), (MONTH(TODAY()) + 1), 1) - 1) - TODAY() < 10, IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 2, 1) - 1, DATE(YEAR(TODAY()), (MONTH(TODAY()) + 2), 1) - 1), IF(MONTH(TODAY()) = 12, DATE((YEAR(TODAY()) + 1), 1, 1) - 1, DATE(YEAR(TODAY()), (MONTH(TODAY()) + 1), 1) - 1)).

Thank you for the 12/31 and 1/31 comment. I had copied and pasted an example and didn't think of the logic that of course the last day of those months is always 31 and I didn't need a formula to calculate it.

I already had a "Data calc" column on this spreadsheet, but this formula only works on Date columns, so I created a new column on the sheet called "Date calc" for any future dates I need in calcs. I put the calculation into one cell rather than two. Don't know if it will speed up the spreadsheet, but I like the cleanliness of it anyways. This cut down the formula in that cell to 1166 characters.

Thank you for helping me "think Smartsheet".

J. Craig Williams

Mattisphere,

Glad I could help.

Craig

tlawrence

Craig,

Thank you! I was able to use your formula to show the last date of the current month as well as the last date of next month.

Is there a way to modify this formula to show the first date of the current month as well? I've been at it for a bit and can't seem to crack it.

Edit - I managed to get it working, for anyone else who is interested...

Current Month Beginning - =IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 0, 1), DATE(YEAR(TODAY(), 12, 31)))

Next Month Beginning - =IF(MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY(), 12, 31)))

J. Craig Williams

I think your formula is wrong for December dates.

The first of the current month is just:

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

there is no complexity to worry about.

Craig

KylerMS

Hi Craig,

What if you wanted to look two months in advance? Would the above formulas hold true?

Thank you for all the content. Much appreciated.

J. Craig Williams

This is the modified formula for the last day of 2 months in advance (example today 2019-10-29 would show 2019-12-31)

=IF(MONTH(Date@row) > 9, DATE(YEAR(Date@row) + 1, MONTH(Date@row) - 9, 1) - 1, DATE(YEAR(Date@row), MONTH(Date@row) + 3, 1) - 1)

For three months, change the two '9's to 8 and the '3' to 4. Rinse and repeat for more future months.

Craig