Formula to compute last working day of following month

[Howardc1001]

i have a date in cell A1 which is the first of a month eg 01/06/2022 (date format dd/mm/yyy)

I need to compute the last working day of the next month, but cannot get it to give me the correct date which should be 29/07/2022(Last working day in July 2022)


It would be appreciated if someone could kindly amend my formula

[BadlySpelledBuoy]

Perhaps this?

=WORKDAY(EOMONTH(A1,1)+1,-1,L2:L13)

BSB

[Howardc1001]

Thanks for the help

Please explain the significance of -1 in the formula +1,-1

[BadlySpelledBuoy]

Excel will calculate the formula from the middle outward, so the EOMONTH function will fire first. EOMONTH(A1,1) will return the last day of the month AFTER the one you have in A1 which in your example is 31st July.

The +1 adds a day on to make 1st August and then the WORKDAY function kicks in using 1st August as its starting point. The -1 in the formula is the "DAYS" argument in the WORKDAY function and will mean it returns a date before the starting point of 1st August. If the starting point -1 day is a week day, and not one of the holiday dates specified, then the formula will return that as the result. However if it falls on a weekend the formula will keep working backward until it finds a date BEFORE the starting point (1st August) that is not a holiday and not a weekend, i.e. the results you're looking for.

Not so easy to explain it would seem... Perhaps the MS Help page for this function will help where I've failed to explain clearly.
https://support.microsoft.com/en-us/...6-60d494efbf33

BSB

[Howardc1001]

Many thanks for the detailed explanation. It makes perfect sense now