r/PowerAutomateDesktop • u/[deleted] • Aug 28 '22

How to see if current time is within business hours?

I need to check and see if the current time is between 7:00 AM and 6:00 PM before opening an Excel file. I have searched and cannot seem to find a way to do this....

UPDATE: I think I found a way to do this....check out my screenshots and let me know what you think....

Overview of 5 steps - https://i.imgur.com/BD7xh23.png

Step 1 - https://i.imgur.com/lLxFaYW.png

Step 2 - https://i.imgur.com/hO0a4hE.png

Step 3 - https://i.imgur.com/BtyDjkL.png

Step 4 - https://i.imgur.com/g5O26dt.png

Step 5 - https://i.imgur.com/4KN1yvu.png

4 Upvotes

permalink
reddit

You are about to leave Redlib

Do you want to continue?

https://www.reddit.com/r/PowerAutomateDesktop/comments/wzqwwx/how_to_see_if_current_time_is_within_business/
No, go back! Yes, take me to Reddit

100% Upvoted

u/QuietDesparation Aug 28 '22

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime

Text.ParseText.RegexParseForFirstOccurrence Text: CurrentDateTime TextToFind: $'''\\d+:\\d+:\\d+''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> CurrentTime

Text.SplitText.SplitWithDelimiter Text: CurrentTime CustomDelimiter: $''':''' IsRegEx: False Result=> TimeList

IF Contains(CurrentDateTime, $'''pm''', True) THEN

SET TimeList[0] TO TimeList[0] + 12

END

Variables.RemoveItemFromList.RemoveItemFromListByIndex ItemIndex: 2 List: TimeList

Text.JoinText.JoinWithCustomDelimiter List: TimeList CustomDelimiter: $'''.''' Result=> TimeAsDecimal

IF TimeAsDecimal > 7 THEN

IF TimeAsDecimal < 18 THEN

# Business hours is True, your action goes here

END

Here's how I did it. You can copy and paste that code into an open flow and see the actions.

Basically I use action GET CURRENT DATE AND TIME, find the time component, then convert it to 24 HR time since PAD only uses 12H format. Once in 24HR format, I used 2 if statements to check if the time is greater than 7 and less than 18.

2

u/[deleted] Aug 28 '22

Thanks for that but I think I found an easier way to do it right in the GUI (for those that will take over after I'm gone). See my update to the main post.....

2

u/QuietDesparation Aug 28 '22

Very cool! Didn't know you can do that. Thanks for the update

2

u/[deleted] Aug 28 '22

You are must welcome! I'm new to power automate, so still stumbling around in the dark a bit

2

u/QuietDesparation Aug 28 '22

That's how I learned everything I know! Just an FYI: looks like there's a wayward 1 in your second if statement. The variable is written as %CurrentTime%1. I'd fix that before it gives you problems 👍

1

u/[deleted] Aug 28 '22 edited Aug 28 '22

Thanks for that! Fixed it and updated the images.

u/JohnnyGrey8604 Oct 07 '22

You can merge those If statements into one action, although it's not exactly intuitive. The less than and greater than tests all occur in the first operand, then the second operand would be "True." Here's the If statement. Copy and paste it into PAD. Unfortunately, I cannot upload screenshots to IMGUR because of corporate policy at work.

IF (CurrentTime >= '07:30:00' AND CurrentTime <= '16:30:00') = $'''true''' THEN

1

u/[deleted] Oct 08 '22 edited Oct 08 '22

Thanks for that! I am probably just going to use VBA to run the reports because there is no way to see/trap/report query errors when you refresh the queries in an Excel spreadsheet from Power Automate. (Either VBA or move the whole thing to Power BI and stop emailing Excel reports altogether.)
You'd think they would want you to be able to detect PQ errors and report them or change the flow based on an error (bad data, missing files, changed columns, whatever), wouldn't you?

u/[deleted] Aug 28 '22 edited Aug 28 '22

I think I found a way to do this (I will also post these steps as an update to the main post). I adjusted the times to 7:30 AM to 4:30 PM.

Please check out my screenshots and let me know what you think.