Future TimeStamp

[Caleb62881177]

Hello,

I want to create a solution where I can hardcode the timestamp of any timestamp that is Friday at 17:00 or greater and less than Monday 08:00 (The next week).

Example:

2023-10-20T17:01:00Z

2023-10-21T19:00:00Z

2023-10-22T15:00:00Z

2023-10-23T07:59:00Z

etc...

Output: 

Hardcode timestamp - 2023-10-24T15:00:00Z

[wskinnermctc]

Is this for this weekend only? Can you explain more about the function of this hardcode timestamp? 

It can be done by basically checking if the day of the week is Saturday or Sunday, then give out hardcode. Check if day of week is Friday, if so, is it after 5pm, then give out hardcode. Check if day of week is Monday, if so, is it before 8 am, then give out hardcode.

It would be easier to make an example if you could talk about your source time and what this is for. Or else I will be making something that works specifically for this text, and might not be best for what you want to use for a flow.

[Caleb62881177]

Hi @wskinnermctc ,

No, it's not only for the weekend, it's basically any timestamp from Friday 17:00 onwards and before Monday 08:00.

The timestamp can be either Friday (Equal or greater than 17:00) or Monday 08:00(Less than).

My source time comes from a SharePoint List Column called created. A record can be created out of business hours from Friday 17:00 onwards until Monday 08:00 the next week. I just need a way to hardcode this timestamp 2023-10-24T15:00:00Z for any timestamp falls into that condition.

2023-10-20T17:01:00Z

2023-10-20T22:01:00Z

2023-10-21T19:00:00Z

2023-10-21T20:00:00Z

2023-10-22T15:00:00Z

2023-10-22T17:00:00Z

2023-10-23T05:59:00Z

2023-10-23T07:59:00Z

etc ...

Output: 

Hardcode timestamp - 2023-10-24T15:00:00Z

[wskinnermctc]

I understand the time range is Friday 5:00pm - Monday 7:59am.

I'm asking about is this flow literally going to be only used for today Oct 20 - through monday Oct 23rd. This weekend.

Or will you be trying to do this again next weekend 27th-30th? and/or the weekend after Nov 3rd-6th? Like will this flow need to adjust as time goes on?

[Caleb62881177]

@wskinnermctc - sorry, I misunderstood you the first time.

The flow would need to adjust to all upcoming weekends

[wskinnermctc · Accepted solution]

Ok this isn't really elegant, but this is all I can come up with on Friday afternoon. It is basically 4 separate expressions checking the datetime value. Then the expressions are all combined into one.

1. If the dayofweek() is Friday (5) and the time hour is greater or equal 17; then add 4 days.
2. If the dayofweek() is Saturday (6) then add 3 days.
3. If the dayofweek() is Sunday (0) then add 2 days.
4. If the dayofweek() is Monday (1) and the time hour is less or equal 7; then add 1 day.

There is also some clean up functions like startofDay() that way it sets the time to midnight and I just added 15 hours so that it will be 3pm/15:00 on the Tuesday.

Expression 1: If day of Week is Friday and after 5:00pm

if(and(equals(dayofWeek(variables('SPCreatedDateTime')),int(5)),greaterOrEquals(int(formatDateTime(variables('SPCreatedDateTime'),'HH')),int(17))),addhours(addDays(startofDay(variables('SPCreatedDateTime')),4),15),variables('SPCreatedDateTime'))

Expression 2: If day of week is Saturday

if(equals(dayofWeek(variables('SPCreatedDateTime')),int(6)),addhours(addDays(startofDay(variables('SPCreatedDateTime')),3),15),variables('SPCreatedDateTime'))

Expression 3: If day of week is Sunday

if(equals(dayofWeek(variables('SPCreatedDateTime')),int(0)),addhours(addDays(startofDay(variables('SPCreatedDateTime')),2),15),variables('SPCreatedDateTime'))

Expression 4: If day of week is Monday and hour is 7 or less

if(and(equals(dayofWeek(variables('SPCreatedDateTime')),int(1)),lessOrEquals(int(formatDateTime(variables('SPCreatedDateTime'),'HH')),int(7))),addhours(addDays(startofDay(variables('SPCreatedDateTime')),1),15),variables('SPCreatedDateTime'))

Combining all of the expressions together, basically removing the last part of each if() and replacing it with the next expression. 

if(and(equals(dayofWeek(variables('SPCreatedDateTime')),int(5)),greaterOrEquals(int(formatDateTime(variables('SPCreatedDateTime'),'HH')),int(17))),addhours(addDays(startofDay(variables('SPCreatedDateTime')),4),15),if(equals(dayofWeek(variables('SPCreatedDateTime')),int(6)),addhours(addDays(startofDay(variables('SPCreatedDateTime')),3),15),if(equals(dayofWeek(variables('SPCreatedDateTime')),int(0)),addhours(addDays(startofDay(variables('SPCreatedDateTime')),2),15),if(and(equals(dayofWeek(variables('SPCreatedDateTime')),int(1)),lessOrEquals(int(formatDateTime(variables('SPCreatedDateTime'),'HH')),int(7))),addhours(addDays(startofDay(variables('SPCreatedDateTime')),1),15),variables('SPCreatedDateTime')))))

Here is a screenshot of the flow I used to make and test this. It has the separate expressions just for testing, they aren't required to be present. I used a variable called SPCreatedDateTime.

The 4 separate expressions are all included in the final compose.

Changing the datetime in my variable I was able to get the output to work correctly.

I went ahead and made a flow using "When an item is created" from SharePoint and then put the expression using the Created datetime. However, I wasn't able to test this since I obviously can't make an item in the future. I basically replaced my variable from the previous example with the sharepoint dynamic content Created.

This uses the SP Created datetime output in the expression. (I was not able to test this.)

Here is the expression with the SharePoint example:

if(and(equals(dayofWeek(triggerOutputs()?['body/Created']),int(5)),greaterOrEquals(int(formatDateTime(triggerOutputs()?['body/Created'],'HH')),int(17))),addhours(addDays(startofDay(triggerOutputs()?['body/Created']),4),15),if(equals(dayofWeek(triggerOutputs()?['body/Created']),int(6)),addhours(addDays(startofDay(triggerOutputs()?['body/Created']),3),15),if(equals(dayofWeek(triggerOutputs()?['body/Created']),int(0)),addhours(addDays(startofDay(triggerOutputs()?['body/Created']),2),15),if(and(equals(dayofWeek(triggerOutputs()?['body/Created']),int(1)),lessOrEquals(int(formatDateTime(triggerOutputs()?['body/Created'],'HH')),int(7))),addhours(addDays(startofDay(triggerOutputs()?['body/Created']),1),15),triggerOutputs()?['body/Created']))))

Hope this works for you,

If you want to look at the functions that were used check Reference Guide for Expression Functions.

if()

equals()

and()

int()

greaterOrEquals()

lessOrEquals()

formatDateTime()

dayofWeek()

startofDay()

addHours()

There might be a more clever way to approach this to make it not such a hammer of an expression, but I can't think of it right now. The benefit of this method is that it would work for any datetime past or future if it was run through. It would change it to the tuesday after that created date.

Good luck!

[Caleb62881177]

@wskinnermctc  - Sorry for taking so long to respond. Your solution worked like a charm and I really appreciate all the effort you have put into figuring this expression.

Your expression is just part of a bigger solution that I was building with many conditions and composes.

Is it possible for you help me with converting my solution from conditions and compose to pure expression solution?

[Caleb62881177]

Hi @wskinnermctc , Can you help me help me with excluding public holidays on the same solution you provided?

Example:

16 December

25 December

1 January 

[wskinnermctc]

@Caleb62881177 I don't think using an expression would be the best approach with the holidays. It would be too many nesting. 

Also, if you think about it, the first expression I made that was just about skipping the weekends doesn't help if a holiday is on a Tuesday and/or some other weird things I probably not thinking about.

You will probably need some method where you list the holiday dates and then compare the date to all of the dates in that list. You can make an array and put in dates, then have the flow go through them. Similarly, you could have like a SharePoint list of dates that include company holidays or something and go through that list.

Are you going to want to include company holidays?

[Caleb62881177]

@wskinnermctc  - No, just public holidays.

[Caleb62881177]

@wskinnermctc - An array with the different dates would works for my solution. I am not sure how to loop through the Get Items and Array with an Apply To Each. 

[Caleb62881177]

@wskinnermctc , I just want to make sure if I am on the right track.