I want to add a formula to my sheet where i can calculate the current month last working date

Shwetaic

I want to add a formula to my sheet where i can calculate the current month last working date from the date entered as the last working date

Will Jeffords

Hi @Shwetaic,

I think this formula may serve:

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

This is basically saying as long as your date is Jan thru Nov, just go to the beginning of the next month and subtract a day, otherwise (if it is Dec), then just put 12/31.

Hope this helps! Let me know if you need further clarification or if it isn't what ye seek.

Best,

Will

Shwetaic

Thank You Will.. But the formula is populating the last day of the month.. I am looking for formula that can populate the Last working day of the month..

Nick Korna

Hi @Shwetaic,

Is something like this what you're after?

Where the formula in Last working day is:

=WORKDAY(DATE(YEAR(Date@row), MONTH(Date@row) + 1, 1), -1)

This calculates the first day of the following month from the date, then uses WORKDAY to find the previous day. The one improvement that could be added is the addition of any non-working days/public holidays on the end of the WORKDAY function, but as these vary by region if you want these adding them I would suggest making a sheet listing them and doing a cross-sheet reference.

Hope this helps, but if I've misunderstood anything or you have any problems/questions then just let us know!