MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have a date field, it gives the last day of the month, three months from an input date (cell d29):
=(EOMONTH(D29,3)
However, it turns out that if this end-of-month date falls on a non-working day, I instead need the calculated date to be the first working day after it. Any ideas?
Wrap it in workday()
An example:
https://exceljet.net/formula/get-last-working-day-in-month
Modify their example to +1 instead of -1
And if you are including public holidays make sure someone has the job of keeping them updated in the workbook.
Can't you use network day formula ?
=Networkdays
Thanks. Looking at it, as I need the last day of the month (if that's a working day), or the first working day of the next month if it's not, I think I need to do that and some IF stuff:
=if(WORKDAY(EOMONTH(D29,3)+1,-1=EOMONTH(D29,3),EOMONTH(D29,3),WORKDAY(EOMONTH(D29,3)+1)
and probably sort the brackets out on that 🙂
And if you are including public holidays make sure someone has the job of keeping them updated in the workbook.
Ah, I thought workdays knew the holidays.
No you need to have a list of public holidays somewhere else and include that range at the end of the workday formula.
If you use a static range then maintenance can be a pig - say you include dates to end of 2022 in A1:A19 then use that range in WORKDAY. Someone will then add 2023 holidays from A20 to A28 but won't update the range in the WORKDAY formula so your formula apparently stops working. I'd suggest using a dynamic named range or just the whole column $A:$A to avert that issue. Whatever you do obviously it's best to keep the holiday list on a separate worksheet from the data and use appropriate protection depending on how fiddly your users are.
You can simplify that.
=WORKDAY(EOMONTH(D29,3)-1,1,holidays)
It looks back 1 day from the end of month and returns the next working day - so if the end of month is a working day you get that, if not then the next one it finds.
I make a named range for holidays as above, it's the easiest way to maintain it.
You can simplify that.
=WORKDAY(EOMONTH(D29,3)-1,1,holidays)
Well that's bleedin' obvious, isn't it *smacks forehead*
As for the holidays, I've got a table with all of them from now to 2050. If this spreadsheet is still in use by then well, some other bugger can sort it
As for the holidays, I’ve got a table with all of them from now to 2050. If this spreadsheet is still in use by then well, some other bugger can sort it
Depends if anyone buggers around with bank hols for the likes of VE day anniversaries, or days off for a coronation (sorry Liz) etc
As long as they're not at the end of Jan, Apr, July or Oct, I don't care :