Unanswered

Advanced Condition - Is Time Between Certain Hours

(0) Share

Report

Posted on by tbomar

Good morning -

I have a small Condition issue for checking if a PowerApp (my trigger) submission is sent between these hours of the day:

4PM - 7AM. If request is sent between these hours (True), the request gets routed to another department. If not between those hours (7:01AM - 3:59PM) request routes to another department.

My condition seems to not work properly on the second line of Condition - 'After Hrs PO Request' less than 07:00 (see below screen shot).

Example, yesterday I submitted a request at 6:44PM (18:44), which first line of condition would be = True

-is 6:44PM or 18:44 greater than 16:00

But, Second line of condition resulted in a false there sending the request down the wrong condition path (No).

Interesting, there isn't logic to simply indicate 'between' a set of numbers or in this case time.

Any better solutions I can use to have this condition work as intended?

I want to avoid creating more conditions in my flow.

Below is screen shot of my flow...

Categories:

Building flows

I have the same question (0)

All responses (14)

Answers (1)

ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@tbomar

I believe the issue is that the condition is evaluating the dates/times as strings. Try formatting the dates so that just the hours show - remove the colon and the minutes. If that doesn't work, try explicitly converting the hours to numbers (just the hour portion).

Was this reply helpful? Yes No

Verified answer

eliotcole

4,363 Moderator on at

Like (0)

Report

Hi, @tbomar, I think this can be done easiest using the 'ticks' function.

Make a ticks out of that initial conversion.
Make a ticks for 4pm that day.
Make a ticks for 7am that day.

Then you can continue on.

The below contains the image if you wanted ANY "IT, Capital (Capex), Fuel & Lube" results to match.

Spoiler (Highlight to read)

Obviously where I have body() contains Eliot, you'd have your extra condition there. Equally obvious ... I couldn't tell what you required from your condition (hence me going mad below 😅) so I've included two versions of how you should build it.

This can of course, all be pulled into one single action, if your flow starts to get long, but this should suffice.

The ticks() function basically changes a date and time into the amount of nanoseconds since January 1, 0001 12:00:00 midnight. As this is an integer, you can more easily work with it on a greater/less than or equal to basis.

The format I chose is the 'o' format which is the ISO8601 standard, it just makes things easier in terms of making the expressions below.

Here's the breakdown of those expressions:

Spoiler (Highlight to read)

4pmVAR

ticks(
 concat(
 formatDateTime(
 body('Convert_time_zone'), 
 'yyyy-MM-dd'
 ), 
 'T16:00:00.0000000Z'
 )
)

7amVAR

ticks(
 concat(
 addDays(
 body('Convert_time_zone'), 
 1, 
 'yyyy-MM-dd'
 ),
 'T07:00:00.0000000Z'
 )
)

ticksVAR

ticks(
 body('Convert_time_zone')
)

4pmVAR ticks( concat( formatDateTime( body('Convert_time_zone'), 'yyyy-MM-dd' ), 'T16:00:00.0000000Z' ) ) 7amVAR ticks( concat( addDays( body('Convert_time_zone'), 1, 'yyyy-MM-dd' ), 'T07:00:00.0000000Z' ) ) ticksVAR ticks( body('Convert_time_zone') )

This has been edited from the original solution which had incorrectly focused on 'next day' and the fact that the condition may have needed more focus.

Was this reply helpful? Yes No

tbomar 87 on at

Like (0)

Report

@ScottShearer ok, based upon my failed flow after removing the semi colon and mins. Assuming this is what you are meaning by "explicitly converting the hours to numbers (just the hour portion)." correct?
Would I change my variable (Time Request Sent) type from string to integer?

Thanks

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@tbomar

See my example below. I formatted the date/time using the HH format and converted the output to an integer.

Was this reply helpful? Yes No
tbomar 87 on at

Like (1)

Report

@eliotcole will try your solution next. More complex than I thought to solve.

Was this reply helpful? Yes No
tbomar 87 on at

Like (0)

Report

@eliotcole I notice your screenshot for - CONVERT TIME ZONE has different fields compared to mine...

Mine:

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report
It is odd that it's a different layout, but the format that I chose is important, since it ensures that throughout the flow it is handling dates and times in the same manner.

The rest of it isn't actually that complex, @tbomar, you just have to look at those variables which I made, and you can see that they're all doing something very simple.

I'll break down the 7amVAR:

addDays() - This function adds 1 day to the time that you've converted, and it is outputting that result purely as the string yyyy-MM-dd (eg. 2022-06-24)

concat() - This is joining strings of text together, namely the addDays() result, and the string listed there, which if done now would show a pure ISO8601 date type string of 2022-06-24T07:00:00.0000000Z

ticks() - This is doing what I explained previously, counting those nanoseconds and returning an integer number

So now you can see, you have:

4amVAR - An integer ticks value for 16:00 today

7amVAR - An integer ticks value for 07:00 tomorrow

ticksVAR - An integer ticks value for the time previously calculated

All of which means that if the integer number inside ticksVAR is between those in 4amVAR and 7amVAR, then it's between the time you wanted it to be between.

Was this reply helpful? Yes No
tbomar 87 on at

Like (1)

Report

disregard

Was this reply helpful? Yes No
tbomar 87 on at

Like (1)

Report

1st attempt worked.
Will wait until after 4pm later today to test if it routes TRUE (Yes) and goes to other department as intended.
Thank You

Was this reply helpful? Yes No
tbomar 87 on at

Like (0)

Report

@eliotcole oh so close. I tried running flow this morning before the 7am/07:00 mark and it still went down the false route. Where it should have went down the True/Yes branch (after 4PM and before 7am).
It must be the next day factor that is confusing the condition. Doing the math in my head, is 625am greater than 4pm, technically that's false from a number/integer perspective (therefore, send the flow down No/False path).

here are the results of flow from this morning around 625-630 am. I simply resubmitted the flow from yesterday that worked for after 4pm.

Was this reply helpful? Yes No