Solved: TimeStamp Minutes excluding out of business Hours

Caleb62881177 · ‎11-02-2023

Hello,

I am trying to write a condition where a timestamp time now is greater than 149 minutes and less than 270 minutes than it's initial timestamp was. Minutes not added when it's out of Business Hours 08:00-17:00 and weekends and before Monday 08:00.

Output:

TimeStamp:

2023-11-02T10:00.00Z

Greater than or Equal:

2023-11-02T12:29.00Z (149 minutes)

Less Than:

2023-11-02T14:30.00Z (270 minutes)

Friday TimeStamp:

2023-11-03T16:00:00Z

Next Week Monday TimeStamp:

2023-11-06T09:29:00:00Z

Monday before 8am:

2023:11:06T07:00:00Z

Only starts adding the minutes when it's 08:00

2023:11:06T10:29:00Z

I hope someone can help.

Chriddle · ‎11-13-2023

I think, if you try to handle this in one expression, this becomes pretty wild 😉

Mainly because I don't know how to exclude weekends within an expression here, I'll introduce a small flow that can handle it.

This is a little naive and certainly more elegant to do, but IMHO easy to understand and extend.

Time:

Compose action with my test data

{
  "Created": "2023-11-06T08:30:00",
  "Now": "2023-11-13T05:30:00"
}

DayRange:

Compose that calculates the difference Start and End in days (probably shorter and more elegant with startOfDay() and ticks 😆)

if(
	equals(
		length(
			split(
				dateDifference(
					formatDateTime(outputs('Time')['Created'], 'yyyy-MM-dd'),
					formatDateTime(outputs('Time')['Now'], 'yyyy-MM-dd')
				),
				'.'
			)
		),
		1
	),
	0,
	int(
		first(
			split(
				dateDifference(
					formatDateTime(outputs('Time')['Created'], 'yyyy-MM-dd'),
					formatDateTime(outputs('Time')['Now'], 'yyyy-MM-dd')
				),
				'.'
			)
		)
	)
)

Days:

Select that creates the days with each start and end time.

From:

range(
	0,
	add(
		outputs('DayRange'),
		1
	)
)

Map Date (just for debug reasons)

formatDateTime(
	addDays(
		outputs('Time')['Created'],
		item()
	),
	'yyyy-MM-dd'
)

Map Start

if(
	contains(
		createArray(6, 0),
		dayOfWeek(
			addDays(
				outputs('Time')['Created'],
				item()
			)
		)
	),
	null,
	if(
		equals(
			item(),
			0
		),
		if(
			less(
				int(
					formatDateTime(
						addDays(
							outputs('Time')['Created'],
							item()
						),
						'HH'
					)
				),
				8
			),
			formatDateTime(
				addDays(
					outputs('Time')['Created'],
					item()
				),
				'yyyy-MM-ddT08:00:00'
			),
			addDays(
				outputs('Time')['Created'],
				item()
			)
		),
		formatDateTime(
			addDays(
				outputs('Time')['Created'],
				item()
			),
			'yyyy-MM-ddT08:00:00'
		)
	)
)

Map End

if(
	contains(
		createArray(6, 0),
		dayOfWeek(
			addDays(
				outputs('Time')['Created'],
				item()
			)
		)
	),
	null,
	if(
		equals(
			item(),
			outputs('DayRange')
		),
		if(
			greaterOrEquals(
				int(
					formatDateTime(
						outputs('Time')['Now'],
						'HH'
					)
				),
				17
			),
			formatDateTime(
				outputs('Time')['Now'],
				'yyyy-MM-ddT17:00:00'
			),
			outputs('Time')['Now']
		),
		formatDateTime(
			addDays(
				outputs('Time')['Created'],
				item()
			),
			'yyyy-MM-ddT17:00:00'
		)
	)
)

DaysWithMinutes:

Select that is the same as Days but with calculated minutes (only important are the minutes, everything else is just for debug reasons)

From: @{body('Days')}

Map Date: item()['Date']

Map Start: item()['Start']

Map End: item()['End']

Map Minutes:

if(
	or(
		equals(
			item()['Start'],
			null
		),
		greater(
			item()['Start'],
			item()['End']
		)	
	),
	0,
	div(
		sub(
			ticks(item()['End']),
			ticks(item()['Start'])
		),
		600000000
	)
)

TotalMinutes:

Compose that sums the minutes

xpath(
    xml(
        json(
            concat(
                '{"Root":{"Days":',
               outputs('DaysWithMinutes'),
                '}}'
            )
        )
    ),
    'sum(//Minutes)'
)

View solution in original post

Chriddle · ‎11-13-2023

That's no problem.

Just add a Compose action with the holidays and enhance the check for workdays

Within "Days" (Start and End) replace the following (that checks for weekend)

contains(
	createArray(6, 0),
	dayOfWeek(
		addDays(
			outputs('Time')['Created'],
			item()
		)
	)
)

with this (that checks for weekend or holidays)

or(
	contains(
		createArray(6, 0),
		dayOfWeek(
			addDays(
				outputs('Time')['Created'],
				item()
			)
		)
	),
	contains(
		outputs('Holidays'),
		formatDateTime(
			addDays(
				outputs('Time')['Created'],
				item()
			),
			'yyyy-MM-dd'
		)
	)
)

View solution in original post

Caleb62881177 · ‎11-03-2023

Can anyone help me?

@ManishSolanki @grantjenkins @FLMike @trice602 @Expiscornovus @BCBuizer @wskinnermctc

wskinnermctc · ‎11-03-2023

@Caleb62881177 You have asked multiple questions about date time over the past week or so. I gave you a long solution in this post Future Time Stamp that dealt with weekends.

However, from looking at your other posts, it seems like you are trying to entirely run some process based on created time or something. I understand how you want it to work, but what you are doing is not a good approach.

You probably need to explain your whole process, because trying to piece together brief datetime functions and conditions is overwhelming and not effective. It also is using people's time to answer and they think they are helping, but they are just contributing to a bad process.

You should change your process to add Status updates columns or something to your SharePoint list. So a column that says like "In-Process" "Overdue" "Completed" to show what status that item is in.

Then you can run flows that use and update the status column. You may need multiple flows, like one that works on when an item is created, a scheduled flow that looks for overdue items, and a flow that acts when an item is modified.

Trying to come up with some frankenstein expression that checks very specific conditions on a datetime is more work than it is worth. I'll admit it is fun to put together expressions like that, but those things are not ideal since it only becomes more complicated as changes are needed.

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']))))

I mean look at this expression. It is not user friendly. Not flexible. And that was just for dealing with the weekend times.

Honestly, I started working on your holiday question, but then I realized it wasn't worth it because it is building on top of bad flow to begin with.

You should rethink your process, sharepoint list design, and flow(s). Design the sharepoint list so that it helps make running flows easier. Also you can use more than 1 flow. Trying to have a single flow do everything will end up over engineering for the sake of having a single flow.

I hope you get this sorted out to work more effectively.

Keep asking questions, and I had fun working on your previous post. But don't be surprised when people are asking more questions about why you need something. What they are really trying to do is come up with an easier method to achieve your results and be more of a long term solution.

Chriddle · ‎11-03-2023

It seems that you understood the question. I didn't. 😂

Caleb62881177 · ‎11-03-2023

@wskinnermctc - Future Timestamp flow was completed and I learned a lot on how to use more expression instead of a bunch of condition. Thank you for your help on Future TimeStamp flow.

This current topic is a completely different flow that serves a different purpose. Basically just check if the Created time stamp falls in between two different stamps and not including Out of Business Hours.

The Holiday one is a lost cause.

Caleb62881177 · ‎11-03-2023

@wskinnermctc - My idea was to write an expression that's output would have the value of the Created TimeStamp with only minutes added, but excluding Out Of Business Hours. The expression out put in place of the variable in the condition.

I hope it's a bit more clearer now.

wskinnermctc · ‎11-03-2023

If you want to convert difference in time between something to minutes, use the ticks expression shown here in this post ticks to find difference between two datetimes.

Here I made a quick example:

Compose - Minutes:

div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000)

Compose - greater than 149 minutes

greater(div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000),int(149))

Compose - less than 270 minutes

less(div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000),int(270))

Compose - greater than 149 and less than 270

and(greater(div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000),int(149)),less(div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000),int(270)))

Here is the output if I use time between 12:00PM and 1:30PM = minutes 90

Minutes Difference 12:00PM and 1:00PM

Here are the outputs if I use time between 12:00PM to 2:30PM = minutes 150

Do you think you can use the ticks() expression to get the minutes between times and use a condition with it?

Caleb62881177 · ‎11-04-2023

@wskinnermctc - Thank you for this link https://powerusers.microsoft.com/t5/General-Power-Automate/Using-the-dateDifference-expression-or-ti...

It will definitely help me with future datetime flows.

I used a expression similar inside a condition, but couldn't figure how to implement to only include Business Hours.

Basically this expression you provided with excluding Out of Business inside it if possible.

and(greater(div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000),int(149)),less(div(sub(ticks(variables('SecondTimeStamp')),ticks(variables('FirstTimeStamp'))),600000000),int(270)))

Caleb62881177 · ‎11-06-2023

@wskinnermctc - I want get 60 minutes from the example below. The flow should subtract the minutes from Second TimeStamp that is greater or equal to 17:00.

wskinnermctc · ‎11-09-2023

@Caleb62881177 I don't understand what you mean by "I want get 60 minutes from the example below. The flow should subtract the minutes from Second TimeStamp that is greater or equal to 17:00" from the previous post.

You have 2 times:

2023-11-03T16:00

2023-11-03T17:30

The first thing you do is get the difference in minutes between the two times. = 90

The second thing you do is convert the second time stamp to show hours and minutes only. 17:30

Then your condition is trying to check if the day of the week of the second time stamp is a Friday. Also trying to check if the second time stamp is greater than or equals 17:00.

The second part of your condition will not work because it can't compare times in that format. It can only compare full datetimes. What you have here will be a comparison of text strings so it would only work if the second time is equal to 17:00.

So walking through what you are showing in your flow. Where are you trying to "get 60 minutes" and where is the subtraction, what does subtraction have to do with this?

I'm not trying to be difficult, I just don't understand the goal.

Caleb62881177 · ‎11-09-2023

@wskinnermctc - Sorry, that flow is a bad example. The flow should be able to subtract all the minutes that is out of Business Hours.

Example:

2023-11-03T16:00

2023-11-03T17:30

Equals 90 min

30 minutes is out of Business

So it should only be equal to 60 minutes

wskinnermctc · ‎11-09-2023

That still doesn't make sense to me.

You are saying the time between is equal to 90 minutes.

30 minutes of that is outside of the business time.

Then you say "So it should only be equal to 60 minutes." What should be equal to 60 minutes??

Are you saying subtract 30 minutes from 17:30 so that it equals 17:00? You want to transform the second time to be 17:00?

Like I don't understand the purpose of this. You have some kind of datetime from an item in sharepoint, lets say it is the Created time of the item.

Are you checking if the created time is inside or outside of business hours? And if it is outside of business hours you want to transform the time?? Transform it to what? Sameday? Next Day?

But you also want to check if that Created time is a certain number of minutes from another time? What is the other time? Is the second time the current utcNow()? Or is the other time some kind of status?

I am confused about the purpose, so it is difficult to create a solution. You have not explained what times are being used for comparison and what kind of transformation is supposed to be happening.

The subtraction of minutes does not make sense to me because I don't know where the subtraction is supposed to happen and what the result is supposed to be.

Please explain the purpose of your flow, not just your problem. Tell me the trigger of the flow. Tell me the source of the dates that need to be compared. What are you trying to accomplish beyond this date comparison? Why is this date comparison necessary for the flow to work?

Caleb62881177 · ‎11-09-2023

@wskinnermctc - The time stamp comes from SharePoint Created column and utcNow is the second time stamp.

The flow I am building triggers once per hour, but have figured out that Trigger condition. The flow check if the created items utcNow is between two different minutes 149 minutes and 249 minutes that represents 50% of the escalated time since the SP item has been created in Business Hours, I check for 50%, 70% then 100%(greater or equals).

If it meets those conditions then it send a email notifying your about the escalation of your SP item.

I hope this make more sense.

wskinnermctc · ‎11-09-2023

So if the item is created at 4:00pm and the flow runs at 6:00pm which is 120 minutes. But the business hours is 5:00pm. You want to return the value of 60 minutes?

And if the flow runs at 10:00am the next day. The difference in business hours would be (4:00pm to 5:00pm 60 minutes) + (8:00am to 10:00am 120 minutes) = 180 business minutes?

Caleb62881177 · ‎11-09-2023

@wskinnermctc - I forgot to mention that the flow is a scheduled flow that trigger from 08:00-17:00 once per hour using a trigger condition from Mon-Fri.

I want to return the value of 60 minutes after the time stamp exceeds 5:00pm

4:00pm to 5pm = 60min is correct and 8:00am - 10:00am (120 min) the next day = 180 min business minutes is correct.

Here is the screenshot of the flow and I used a Manual trigger here.

Hopefully this also helps.

wskinnermctc · ‎11-09-2023

So I thought of a different way to approach this, but need to know if that is something you are willing to work with.

I don't know exactly what your conditions about 50,75,100 but using them as a starting point.

In your SharePoint list could you add some extra columns? I'm thinking that you have columns for each time window. (You could just use 1 extra time column, but separating by your 50 70 100 would help.) Then you also have a time status or notification status column.

So when an item is created, you fill in those columns with the time windows that will be checked. You can either do this when the item is created, or as a secondary update to the item when it is created.

It uses the current time when the item is created to populate those additional columns by adding minutes.

Before the field is populated you could check if that time will be outside of business hours and if so, then how many minutes to add.

Then your scheduled flow only needs to check those time windows and/or that status column.

This isn't wildly different than what you are doing now; except it is more like front loading those times to be checked. The benefit of this is the ability to use the base created date to add times. Then all you have to do is put conditions on that added time and see if it needs to be adjusted.

It would be more similar to the first time I helped you and it changed the time to Tuesday.

Does that seem like a direction you could go?

Caleb62881177 · ‎11-09-2023

@wskinnermctc - 50, 70, 100 is the percentage of 6 hours escalation.

Example:

50 % = 180 min

70 % = 270 min

100% = 360 min

I'll take any suggestion/guidance that will work at this point.

Caleb62881177 · ‎11-12-2023

@wskinnermctc - I still struggling to figure how exactly to do your SharePoint suggestion.

Do you think that this expression below might work?

div(sub(ticks(variables('SecondTimeStamp')), ticks(variables('FirstTimeStamp'))), 600000000) -
(mul(sub(dayOfWeek(variables('FirstTimeStamp')), 1), 2880) +
mul(sub(sub(int(formatDateTime(variables('FirstTimeStamp'), 'HH')), 17), if(greater(sub(int(formatDateTime(variables('FirstTimeStamp'), 'mm')), 0), 0), 1, 0)), 60) +
mul(sub(dayOfWeek(variables('SecondTimeStamp')), 1), 2880) +
mul(sub(sub(int(formatDateTime(variables('SecondTimeStamp'), 'HH')), 17), if(greater(sub(int(formatDateTime(variables('SecondTimeStamp'), 'mm')), 0), 0), 1, 0)), 60) +
sub(int(formatDateTime(variables('SecondTimeStamp'), 'mm')), 0))