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

[mahendra0393]

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

[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!

[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!

[LindonB · Accepted solution]

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!