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

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

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

AdHours Only on Business Days

(0) ShareShare
ReportReport
Posted on by Caleb62881177 789

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

Categories:
Building flows
  • v-xiaochen-msft Profile Picture
    v-xiaochen-msft Microsoft Employee on at

    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 789 on at

    @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 789 on at

    @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 Microsoft Employee on at

    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 789 on at

    @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 Microsoft Employee on at

    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 789 on at

    @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 789 on at

    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 789 on at

    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

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Mohsin Ali Profile Picture

Mohsin Ali 425

#2
11manish Profile Picture

11manish 323 Super User 2026 Season 2

#3
M Bilal Khan Profile Picture

M Bilal Khan 274

Last 30 days Overall leaderboard

Featured topics

Known issue with browser automation in Power Automate for Desktop on Google Chrome 146 and 147
Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans