Need help to rectify logic to show correct values for VD-1 calculation

[DD]

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.

[Christina_H]

Are you just trying to find the previous date from the list?  The easiest way to do that would be to sort the dates and then use a multirow formula to get the value from the previous row.  If all the dates you're interested in are already in that list, it seems like a waste of time to try calculating the date you want.

[DD]

Sounds good, let me try. 

[RobertOdera]

Hi @DD 

1. Happy to show you how to use generate rows to flag all weekend days within any adaptive date range

2. Kindly share what the Holidays are that you want to flag

If we have 1 and 2 above, the rest will be doable by leveraging Find Replace, Joins, Multirow, etc - Cheers!

[DD]

HI Robert,

please find the holiday Listt 

1/26/2023

4/7/2023

5/1/2023

6/29/2023

8/15/2023

9/18/2023

10/2/2023

10/23/2023

10/24/2023

11/14/2023

11/1/2023

12/25/2023

[DD]

i need this logic as I need to calculate for last week, last month end date and previous year end