Hi All, Need a help with the below workflow please.
Used Case- Require VD-1 date excluding weekends and Dates (Holiday days) which are not in the report.
Issue - Workflow created works good for days not in report (which are treated as holiday days) and holds good even with excluding weekends but results not matching when there is no holiday day in the week in the report.
Sample data
| | Expected results | |
| Holiday Dates | VD | Expected report date VD-1 | Comments |
| 16/01/2023 | 17/01/2023 | 13/01/2023 | If holiday take Holiday Date -3 for Monday and -1 if Holiday falls between Tuesday - Friday |
| | 18/01/2023 | 17/01/2023 | |
| 19/01/2023 | 20/01/2023 | 18/01/2023 | |
| | 23/01/2023 | 20/01/2023 | |
| | 24/01/2023 | 23/01/2023 | |
| | 25/01/2023 | 24/01/2023 | |
Wanted a logic which helps in auto picking previous day data date when it's a holiday or a weekend.
Logic created -
if [Yesterday Date] = "Mon" and [Max_Previous Date 1] in ([Date]) then [Max_Previous Date 1]
elseif [Yesterday Date] = "Mon" and [Max_Previous Date 1] not in ([Date]) then datetimeformat(datetimeadd([Max_Previous Date 1],-3,"days"),"%m/%d/%Y")
elseif [Yesterday Date] = "Tue" and [Max_Previous Date 1] in ([Date]) then [Max_Previous Date 1]
elseif [Yesterday Date] = "Tue" and [Max_Previous Date 1] not in ([Date]) then datetimeformat(datetimeadd([Max_Previous Date 1],-1,"days"),"%m/%d/%Y")
elseif [Yesterday Date] = "Wed" and [Max_Previous Date 1] in ([Date]) then [Max_Previous Date 1]
elseif [Yesterday Date] = "Wed" and [Max_Previous Date 1] not in ([Date]) then datetimeformat(datetimeadd([Max_Previous Date 1],-1,"days"),"%m/%d/%Y")
elseif [Yesterday Date] = "Thu" and [Max_Previous Date 1] in ([Date]) then [Max_Previous Date 1]
elseif [Yesterday Date] = "Thu" and [Max_Previous Date 1] not in ([Date]) then datetimeformat(datetimeadd([Max_Previous Date 1],-1,"days"),"%m/%d/%Y")
elseif [Yesterday Date] = "Fri" and [Max_Previous Date 1] in ([Date]) then datetimeformat([Max_Previous Date 1],"%m/%d/%Y")
elseif [Yesterday Date] = "Fri" and [Max_Previous Date 1] not in ([Date]) then datetimeformat(datetimeadd([Max_Previous Date 1],-1,"days"),"%m/%d/%Y")
else datetimeformat([Max_Previous Date 1],"%m/%d/%Y")
endif
Results am getting incorrect from Workflow.
Please let me know what went wrong as i am unable to figure it out and am being suggested with other way of doing that would be to use Holiday calendar as an input tool and create workflow and i do not want as it should generate continually using the data this way without manual touch. Considering not to amend Holiday calendar every year.
