Allocate hours into months based on given date range

UrbanNomad

Hi All,

I want to allocate hours equally based on a given date range, in excel I could get it work using Eomonth, in Smartsheet, I'm struggling to achieve.

I have attached the image of the formula I got it working in Excel, how do I replicate using Smartsheet?

Ben Goldblatt

Hi @UrbanNomad,

Smartsheet doesn't currently have an EOMONTH function available, but I highly recommend submitting a Product Enhancement Request to see about having this considered as a possibility for future development.

In the meantime, you could use something like this to populate the last day of the month from a specified date:

=IF(MONTH([End Date]@row) < 12, DATE(YEAR([End Date]@row), MONTH([End Date]@row) + 1, 1) - 1, DATE(YEAR([End Date]@row), 12, 31))

You would need to replace the @row reference to reference the specific date cell in your sheet, but you may be able to incorporate this into your existing formula in Smartsheet. More information on these functions can be found in the following Help articles from the Smartsheet Learning Center:

• DATE Function
• YEAR Function
• MONTH Function

I hope that helps!

Thanks,

Ben

UrbanNomad

Hi @Ben G ,

I tried incorporating the formula, it is giving me values but It they are incorrect, looks like I'm almost there.

Any insights?

Ben Goldblatt

Hi @UrbanNomad,

I've been playing around with this as well and I haven't been able to come up with a working formula that will distribute the hours accurately and equally based on the date range. You may want to consider scheduling a Pro Desk appointment to see if one of our experts can assist further. If you're not familiar with Pro Desk, this service delivers practical one-on-one coaching sessions (30 minutes per session, 10 sessions per year) with a Smartsheet expert to accelerate your setup time and help you optimize your projects, programs, and processes. A Pro Desk expert will provide coaching, guidance, best practices, and resources for further development.

To view available session dates and times and to schedule a Pro Desk appointment, visit https://www.smartsheet.com/pro-desk. I will send you a direct message with your Pro Desk company Access code in case you're unsure of what that is.

Thanks,

Ben