EOMonth Formula work around

Jim Mika

To whom it may concern,

I have used the attached formula in excel for monthly projections and was trying to figure out if and how I might be able to do the same in Smartsheet. This formula has an end of month function, which I know is not available in Smartsheet. Below is the formula and sample data. Any help would be greatly apricated.

Thanks

Jim

=$G9*
((MAX(H$8-$C9,0)-MAX(EOMONTH(H$8,-1)-$C9,0))
-(MAX(H$8-$D9,0)-MAX(EOMONTH(H$8,-1)-$D9,0))
+(EOMONTH(H$8,0)=EOMONTH($C9,0)))

Heather Duff

Hi! You might find this thread helpful:

https://community.smartsheet.com/discussion/comment/244254#Comment_244254

Jim Mika

Thanks for you reply. I just need the end of the month without any time added.

Paul Newcome

To generate the last day of the month, you would use something like this...

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 1, 1), DATE(YEAR([Date Column]@row, 12, 31))

CAH

I found this same post for Excel and have converted the formula as follows for January 2022 - adjust dates as required.

=[Revenue / Day]@row * ((MAX(DATE(2022, 1, 31) - [Start Date]@row, 0) - MAX(DATE(2021, 12, 31) - [Start Date]@row, 0)) - (MAX(DATE(2022, 1, 31) - [End Date]@row, 0) - MAX(DATE(2021, 12, 31) - [End Date]@row, 0)) + IF(MONTH(DATE(2022, 1, 31)) = MONTH([Start Date]@row), 1))