last working day of the month

[Amit Srivastava]

Hi All while calculating the average balance for my working I need to use the last working day of the month, however If the month end does not fall on the last working day, the average balance is not correct. For example, if 29th is the last working day of a 31-day month, the average balance provided would only account for 29 out of 31 days instead of full month aggregated average balance. I do not have the dates in the file I am getting I am just using the month in consideration as my base (for ex if I am working on Jun 2017 then I need to calculate the last working day for the same.) How can I get the last working day of the month if the above needs to be considered.

Also as a separate request if I need to update the formula for any holidays that fall within the said month how to incorporate the same. Kind Regards, Amit Srivastava

<tbody>
</tbody><colgroup><col></colgroup>

 

[Tetra201]

The following formula returns the last workday of the current month (assuming you have your holiday dates in a range named Holidays):

=WORKDAY(EOMONTH(TODAY(),0)+1,-1,Holidays)