Log In
Register
Home
September 9, 2020
Offline
Hi, there,
I have, in power query, the list of all the days in the year 2024.
I would like to indicate, in an additional column and for each week, Friday and if Friday is a public holiday then Thursday (or Wednesday if Thursday is a public holiday).
Can you give me an idea?
BR,
Lionel
Moderators
January 31, 2022
Offline
To keep it fairly simple I would perform a few consecutive merges, comparing first all Fridays against the holidays. Then keep the date or deduct one day in a new date column (Custom). This will than contain all Fridays and some Thursdays. Repeat the merge, again comparing this Custom date column with holidays. Keep those dates or again deduct one days if it is a holiday, creating a new date column Custom.1. Remove all other columns.
You now end up with a list of dates, mostly Fridays, an occasional Thursday and one Wednesday, not being holidays.
Is that what you had in mind?
See attached.
September 9, 2020
Offline
Hi Riny,
It's actually an identical process that I wanted to put in place but I can't manage it. I feel like I'm doing something complicated when there could perhaps be a simple solution.
NB: I realise that I've put in an xlsb file which is probably unreadable. Here's the xlsx version.
Lionel
Moderators
January 31, 2022
Offline
You can open the xlsb file by right-clicking and then Save link as and than point to a folder on your system. That's how I did it. Forgot to save the file as an xlsx before uploading.
Here it is!
September 9, 2020
Offline
Thanks for the recording tip.
I can see how you did it. It's a good solution and it works perfectly!
I hope I never have a bank holiday on Wednesday, otherwise I'll have to repeat the operation.
Moderators
January 31, 2022
Offline
'Good solution' is a big word. Since you would only do this once a year it would almost be quicker to have a list of all Friday's, mark all holidays on it and then correct the list of Fridays manually.
Almost can't believe I'm suggesting this but sometimes it simpler to do things without too much automation 🙂
September 9, 2020
Offline
In fact, this treatment is just the tip of the iceberg. So it's not possible to carry out the treatment manually. So I'm trying to replace Excel formulas with power queries.
I'm sure we can come up with something better. This is the beginning of the road to a solution.
Lionel
1 Guest(s)
