web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate - Building Flows / AdHours Only on Busine...
Power Automate - Building Flows
Answered

AdHours Only on Business Days

(0) ShareShare
ReportReport
Posted on by Caleb62881177 765

Morning Everyone,

 

I am trying to write an expression that will only add hours to a record created in a SharePoint List using the Created column as the starting date. The expression should only add time to during Business Hours and exclude non Business Hours.

 

Here is What I got so far:

SharePoint List Created column

addHours(triggerOutputs()?['body/Created'],6)

 

Example:

 

I want the flow only to add those hours in Business Hours Example: How the flow is currently operating now : 16:00 would add 6 hours = 22:00 Instead I want to add the 6 hours like this: 16:00 - 13:00 (Next day or Monday if the last Business day was Friday)

 

Hopefully someone can help, please.

 

Example

I have the same question (0)
  • v-xiaochen-msft Profile Picture
    v-xiaochen-msft on at
    Re: AdHours Only on Business Days

    Hi @Caleb62881177 ,

     

    According to your description, you want to AddHours Only on Business Days.

    Here are the steps you can refer to :

    1. I use the variable to test and you can use your triggerOutputs()?['body/Created'] to replace my "   variables('DateTimeTest')  ":

    vxiaochenmsft_0-1696930827337.png

     



    And I assume that your working hours are 9.00-18.00 from Monday to Friday.So you may need to modify the corresponding time in my formula.



    (2)And this is my Logic you can refer to :
    [1] If it is Saturday and Sunday, return 9.00+6hours for the following Monday

    [2] If <=9.00, 9.00+6hours of the current day is returned

    [3] If >=9.00 and <=12.00, the current time +6hours is returned

    [4] If it is a Friday, and > 12.00 & <18.00 returns the following Monday 9.00+6- (current time 18.00 of the same day) [5] If it is Friday and >=18.00, returns 9.00+6hours of the following Monday

    [6] If >=18.00, it returns 9.00+6hours the next day.

    [7] Otherwise, return 9.00+6- the next day (18.00 on the same day - current time)

     

    And i use this formula to get the according value:
    dayOfWeek(variables('DateTimeTest')) :    get the weekday number 

     

    formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT9:00:00Z')     :Get 9.00 for the date

     

    greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z') 😞  Determine if the time is > = 18.00 on the same day

     

    dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00')  : Get the hours, minutes, and seconds if you want to add to the next day, This return like "04:35:00", so we can use the split() function to get the hours,minutes and seconds we need .

     

    formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT09:00:00' )  : Get next Monday 9.00





    (3)And i use this code in the compose action to test,you can modify and refine this code according to your logic:


    if(contains(createArray(6,0),dayOfWeek(variables('DateTimeTest')))

    , addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT09:00:00' )  ,6),

    if(lessOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT09:00:00Z') ) ,addHours(formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT09:00:00Z'),6)  ,

    if(and(greater(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT09:00:00Z') ) ,lessOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT12:00:00Z') ) ) 

    ,addHours(variables('DateTimeTest'),6),

    if(and(equals(dayOfWeek(variables('DateTimeTest')),5) ,greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z') ) ),addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT09:00:00' )  ,6),

    if(and(equals(dayOfWeek(variables('DateTimeTest')),5),greater(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT12:00:00Z') ) 

    , less(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z') ) 

     ),

     addSeconds(addMinutes(addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ),'yyyy-MM-ddT09:00:00' ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00'), '06:00:00'),':')[0]) 

    ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00')

    , '06:00:00'),':')[1]) ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00')

    , '06:00:00'),':')[2]) )

            ,

    if(greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z') ) 

    ,addHours(formatDateTime(addDays(variables('DateTimeTest'),1) ,'yyyy-MM-ddT09:00:00Z') ,6) ,

    addSeconds(addMinutes(addHours(formatDateTime(addDays(variables('DateTimeTest'),1) ,'yyyy-MM-ddT09:00:00Z') ,int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00')

    , '06:00:00'),':')[0]) ),int(split(dateDifference(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00'), '06:00:00'),':')[1]) ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT18:00:00Z')), '06:00:00'), '06:00:00'),':')[2]) )

    )      )))))





    The result is as follows:

    vxiaochenmsft_1-1696930827341.png

     

    Best Regards,

    Wearsky

  • Caleb62881177 Profile Picture
    Caleb62881177 765 on at
    Re: AdHours Only on Business Days

    @v-xiaochen-msft Sorry for responding so late.

     

    I changed the Business hours from 08:00 - 17:00 and changed the  6 hours to 7 hours.

    The Created time I get from a SharePoint List record. The record was created at 10:43 am today and I got the output of 17:43 instead of 08:43 the next day.

     

    I used the following expression:

     

    if(contains(createArray(6,0),dayOfWeek(variables('DateTimeTest')))

    , addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT08:00:00' )  ,7),

    if(lessOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT08:00:00Z') ) ,addHours(formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT08:00:00Z'),7)  ,

    if(and(greater(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT08:00:00Z') ) ,lessOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT12:00:00Z') ) )

    ,addHours(variables('DateTimeTest'),7),

    if(and(equals(dayOfWeek(variables('DateTimeTest')),5) ,greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z') ) ),addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT08:00:00' )  ,7),

    if(and(equals(dayOfWeek(variables('DateTimeTest')),5),greater(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT12:00:00Z') )

    , less(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z') )

     ),

     addSeconds(addMinutes(addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ),'yyyy-MM-ddT08:00:00' ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'), '07:00:00'),':')[0])

    ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00')

    , '07:00:00'),':')[1]) ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00')

    , '07:00:00'),':')[2]) )

            ,

    if(greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z') )

    ,addHours(formatDateTime(addDays(variables('DateTimeTest'),1) ,'yyyy-MM-ddT08:00:00Z') ,7) ,

    addSeconds(addMinutes(addHours(formatDateTime(addDays(variables('DateTimeTest'),1) ,'yyyy-MM-ddT08:00:00Z') ,int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00')

    , '07:00:00'),':')[0]) ),int(split(dateDifference(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'), '07:00:00'),':')[1]) ),int(split(dateDifference( dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'), '07:00:00'),':')[2]) )

    )      )))))

  • Caleb62881177 Profile Picture
    Caleb62881177 765 on at
    Re: AdHours Only on Business Days

    @v-xiaochen-msft  I forgot to mention I created variable that also has the same name variable as you used DateTimeTest.

    I gave the variable the value of triggerOutputs()?['body/Created']

  • Verified answer
    v-xiaochen-msft Profile Picture
    v-xiaochen-msft on at
    Re: AdHours Only on Business Days

    Hi @Caleb62881177 ,

     

    Thanks for your quick response !   Your business hours from 08:00 - 17:00 and changed the  6 hours to 7 hours.

    I test it in my side , you can change the code to this:

     

    if(contains(createArray(6,0),dayOfWeek(variables('DateTimeTest')))

    , addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT08:00:00' )  ,7),

     

    if(lessOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT08:00:00Z') ) ,addHours(formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT08:00:00Z'),7)  ,

     

    if(and(greater(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT08:00:00Z') ) ,lessOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT10:00:00Z') ) )

    ,addHours(variables('DateTimeTest'),7),

    if(and(equals(dayOfWeek(variables('DateTimeTest')),5) ,greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z') ) ),addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ) ,'yyyy-MM-ddT08:00:00' )  ,7),

    if(and(equals(dayOfWeek(variables('DateTimeTest')),5),greater(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT10:00:00Z') )

    , less(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z') )

     ),

     addSeconds(addMinutes(addHours(formatDateTime(addDays(variables('DateTimeTest'),sub(8,if(equals(dayOfWeek(variables('DateTimeTest')),0),7,dayOfWeek(variables('DateTimeTest')) ) ) ),'yyyy-MM-ddT08:00:00' ),int(split(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'),':')[0])

     

    ),int(split(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'),':')[1])

     ),int(split(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'),':')[2])

     )

            ,

    if(greaterOrEquals(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z') )

    ,addHours(formatDateTime(addDays(variables('DateTimeTest'),1) ,'yyyy-MM-ddT08:00:00Z') ,7) ,

     

    addSeconds(addMinutes(addHours(formatDateTime(addDays(variables('DateTimeTest'),1) ,'yyyy-MM-ddT08:00:00Z') ,int(split(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'),':')[0])

     ),int(split(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'),':')[1])

     ),int(split(dateDifference(dateDifference(variables('DateTimeTest'),formatDateTime(variables('DateTimeTest'),'yyyy-MM-ddT17:00:00Z')), '07:00:00'),':')[2])

     )

    )     )))))

     

     

    Then we can get the result as follows:

    vxiaochenmsft_0-1697166533125.png

     

    Best Regards,

    Wearsky

  • Caleb62881177 Profile Picture
    Caleb62881177 765 on at
    Re: AdHours Only on Business Days

    @v-xiaochen-msft  Sorry for responding now.

     

    I get the time of the record from a SharePoint List Column called Created. The time the record was created is 10:46am. After the flow runs and adds the 7 hours the SharePoint column is updated with that expressions time 17:46.

     

    Output of the flow:

     

    var.jpg

     

    compose.jpg

  • v-xiaochen-msft Profile Picture
    v-xiaochen-msft on at
    Re: AdHours Only on Business Days

    Hi @Caleb62881177 ,

     

    Thanks for your quick response !

    You said that your item created in 10:46am, but in your screenshot , the variable is:

    vxiaochenmsft_0-1697504972912.png

     

    So you need to make sure if you get the correct time, and if you enter "2023-10-12T10:46:18Z", i test it in my side , it will return "2023-10-13T08:46:18.0000000Z":

    vxiaochenmsft_1-1697504972914.png

     

    Best Regards,

    Wearsky

  • Caleb62881177 Profile Picture
    Caleb62881177 765 on at
    Re: AdHours Only on Business Days

    @v-xiaochen-msft I have made changes to the hours I wanted to add to the Created time and expression doesn't exclude Out of Business hours like the results you get.

  • Caleb62881177 Profile Picture
    Caleb62881177 765 on at
    Re: AdHours Only on Business Days

    Hi @v-xiaochen-msft , 

    I was thinking about the calculations for the solution and I figured out that for Monday to Friday you add 16 hours to the time and from Friday to Monday you add 63 hours.

  • Caleb62881177 Profile Picture
    Caleb62881177 765 on at
    Re: AdHours Only on Business Days

    Sorry, It is suppose to be 15 hours for Monday to Friday instead 16 hours.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Coming soon: forum hierarchy changes
Welcome to the Power Platform Communities

Quick Links

Coming soon: forum hierarchy changes

In our never-ending quest to improve we are simplifying the forum hierarchy…

Chiara Carbone – Community Spotlight

We are honored to recognize Chiara Carbone as our Community Spotlight for November…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 535 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 348 Moderator

#3
developerAJ Profile Picture

developerAJ 262

Last 30 days Overall leaderboard

Featured topics

Share your ideas and struggles with building Power Automate flows!
Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans