Sending an Email on Friday if someone goes on Holidays the following Monday

[Deano478]

Hi Folks,

I'm currently working on something pretty cool and I just hit a bit of a roadblock that I hope someone can shed some light on.

Essentially I want to be send out an email to someone on Friday if they go on leave the following Monday. I'm fine with the email tool my issue is with trying to find the previous Friday I'm just not sure about how I can calculate/find this.

So in the case of Dean Below I'd wanna email him on the Friday prior to his holiday time which would be the 14th

Here is a small anonymous sample of the data:

ID	Name	Date Entered	Day of Week	Approved
1	Dean	17/04/2023	Monday	Y
2	Sean	05/04/2023	Wednesday	Y
3	Mark	31/03/2021	Friday	Y

If it's a complicated ask I do apologise as I don't want to be taking up peoples time either.

Cheers for the help in advance.

[mceleavey · Accepted solution]

Hi @Deano478 ,

I've attached the workflow to do this.

Basically, I have built a calculation as follows:

if [Approved]="Y" then datetimeadd([Date Entered],
(if DateTimeFormat([Date Entered],"%u")="5" then -7 elseif
DateTimeFormat([Date Entered],"%u")="4" then -6 elseif
DateTimeFormat([Date Entered],"%u")="3" then -5 elseif
DateTimeFormat([Date Entered],"%u")="2" then -4 elseif
DateTimeFormat([Date Entered],"%u")="1" then -3 elseif
DateTimeFormat([Date Entered],"%u")="7" then -2 elseif
DateTimeFormat([Date Entered],"%u")="6" then -1 else 0 endif),
"Days") else "Error" endif

This check first if the record has been approved. If so, then pull the number of the day (%u) from the date field, and apply a datetimeadd function to roll it back a given number of days to the prior friday according to the number of the day. So, if it's Thursday and the record is approved, move it back 6 days.

This gives me the following:

I hope this helps,

M.

[Deano478]

@mceleavey That was extremely helpful I was able to tweak your approach slightly and got the desired output cheers for the assistance

[mceleavey]