How to check for holidays in loop and populate date before holiday date.

I have below holiday list. In new column I want to populate date (Holiday-1) if holiday date match with current date. if there is consecutive days in holiday list then populate date( holiday start date-1) and if holiday on Monday populate Friday date which will be holiday-2.

Holiday
2024-01-01
2024-01-26
2024-03-29
2024-04-01
2024-04-25
2024-12-25
2024-12-26
2024-07-24
2024-07-23
2024-07-22

Workflow

Accepted answers

LindonB

For the first (wanting to only apply the shift if the current date = holiday), you can either add a filter at the start to check:
DATETIMETODAY()=[Holiday]
The will lead to the final output/browse only containing the shifted date for today.

Otherwise, if you want to return the full dataset but only shift dates when current date = holiday, then adjust the formula in the workflow to be....

If DateTimeToday()!=[Holiday] THEN [Holiday]
ELSEIF DateTimeFormat([Min Eligible Date],'%A')='Monday' THEN DateTimeAdd([Min Eligible Date],-3,'days')
ELSE DateTimeAdd([Min Eligible Date],-1,'days') ENDIF

Since today is not in the Holiday list, the results will be the same as the original data in this case.

For the second, I believe the workflow is doing this. It will find the groupings based on consecutive days (July 22-24). Then it looks at the minimum in that group (July 22). Then it returns the prior Friday if the min in a Monday or the last day otherwise. (Note that July 19 wasn't listed in your data, but the rest would by July 18 if it was.) Again, I might build in more logic for weekends, and Friday holidays currently don't have any extra logic.

Does that help or is there a step I'm misunderstanding?

Cheers!

All comments

LindonB

Hi there,

See attached. I added the dates into different groups based on whether or not they are consecutive and then applied the logic based on the min date from that group.

I wasn't sure if you just wanted to apply to the whole table or only apply the shift if the date "match[es] with current date" (as stated in the question). If you did intend for this, just add a IF datetimetoday() = [Holiday] ELSE around the formula.

Also, not sure if it'll happen, but you might want to add logic for if the holiday ever falls on the weekend.

Hope that helps!

Community Post Solution 1298721.yxmd

mahendra0393

Thanks Lindon for your quick response. I want to apply shift if current date matches with Holiday. Also, I need to consider Friday and Monday holiday under consecutive group. How can I do that for i.e 19 July and 22 July is holiday in the provided list. So shift date should have 18 July So 19-24 july should come under group 6.

CoG

Here is a variation on @LindonB 's workflow that groups consecutive holidays across weekends. This workflow is dynamic, based on the holidays used as input, but you can just as easily adapt this to pre-calculate what date each holiday should map to and then you will only need to handle

Happy Solving!

Sample Workflow.yxmd

jmbarc

Hi Lindon,

Thanks for the solution. I am experiencing a similar issue where i am trying to roll the end date to the next good business day. I have been able to alter your flow to roll the dates.

The issue i am having is when the Holiday date is on a Fri and Mon the grouping formula is not counting these as consecutive days. Would it be simple to amend this formula to state if date = sat or sun then ignore? The weekends will not be present in my file.

Thanks in advance,

Jack

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703