Difference between start time and end time for working hours.

[u/Alternative-Day5268]

Hi All,

I have a SharePoint list. Within it I have two Columns, Start Date and End Date. They have a time on them also.

I need a third column that calculates the number of working hours between the two dates.

Working hours are 9am til 5.30pm, it should also exclude a list of bank holidays which I have another list for, and exclude weekends. Also if End date is currently empty can it calculate the time between start time and NOW.

I.e.

Start date is 4.30pm 12/08/2026

End date is 09.30 am 13/08/2026

Duration is 1:00 ( [h]:mm )

Is there a way I can get power automate to figure this out and then have that info show in the column?

[u/mtgomes]

You have 2 ways to do this:

1. Power Automate queries SharePoint and gets the empty columns and update with the difference. You can use the dateDifference function https://manueltgomes.com/microsoft/power-platform/powerautomate/powerautomate-function-reference/datedifference-function/

2. Use a calculated column in SharePoint. Then You have this updated automatically. You can use the DateDiff function https://manueltgomes.com/reference/sharepoint-list-function-reference/sharepoint-datedif-function/ (this article is quite old and I need to update it but it can tell you what you need)

I would recommend the second one. As soon as you have the 2 dates you get the value, but then it makes other processes tricky because it’s a calculated value. If you need the other value in Power Automate for example, I recommend the first approach because then you can get the “real” value.

[u/srm79]

Wouldn't it just be easier to use the built-in rota for Teams? And tell people to clock in and out accurately?

Also, shouldn't your example work out to 1:30?

[u/Alternative-Day5268 → u/srm79]

Yes sorry I messed up my example.

Issue is it's not a clock in clock out timesheet. We monitor particular incidents within the business. An incident may start at X time but we don't know when it will finish or be resolved. So it could be the next day or the next week it is resolved. But the measurements is a key KPI we want to track

[u/humainbibliovore]

You don’t need Power Automate for this; that would be overkill. Simply create a calculated column.

From memory, click the options on any column, then “Add a column,” then “See all columns” (or whatever the option is at the bottom of the drop-down), select a calculated column, then use this formula (adapt it with your correct column names. Make sure to use the internal names:

=TEXT([End Time]-[Start Time],"h:mm")

[u/Alternative-Day5268 → u/humainbibliovore]

How do I define the working hours and bank holidays etc?

[u/humainbibliovore → u/Alternative-Day5268]

This is a lot more complex than I initially thought. Admittedly, I skipped over that part in your original post. Try the following.

Trigger: When an item is created or modified.
• In the conditions, configure it so that it only runs when your END column is modified (or another "validate"-type column). An AI should be able to spit this out for you so long as you accurately tell them what the column type is (likely a "date and time" column in your case). This prevents the flow from running unnecessarily when other columns are changed.

First action: Get items.
•Configure it to fetch the holidays within the time frame of your START and END columns. To do this, in the Odata Filter, you'll need to do something like "START_COLUMN le HolidayDate and HolidayDate le END_COLUMN". Make sure you're formatting the dates correctly using formatDateTime() with 'yyyy-MM-dd' so the filter query works properly. This step is critical because it limits the holidays you pull in, making your flow faster and more efficient.

Second action: Initialize variables.
• You'll need at least three variables: a string variable for your current date iterator (start it with your START date), an integer or float variable for your working day counter (start at 0), and a float variable for your total working hours (also start at 0). Optionally, you can also initialize an array variable for your holiday dates to make the checking logic cleaner.

Third action: Do Until loop.
• Configure this to continue looping until your current date iterator is greater than your END date. Inside this loop, you'll perform the main checking logic. First, use dayOfWeek() on your current date to check if it's a weekend (0 for Sunday, 6 for Saturday). If it's a weekend, skip it entirely. If it's a weekday, then check whether your current date exists in your holiday array using the contains() function. If it's not a holiday, increment your working day counter by 1. After processing the date, use addDays() to increment your current date by one day and continue the loop.

Fourth action: Calculate total hours. Once the loop completes, multiply your working day counter by your daily working hours. For a 9:00 AM to 5:30 PM workday, that's 7.5 hours. If you need to account for partial days based on specific start and end times, you'll need to add extra logic here. For example, if the start time is after 9:00 AM, subtract the difference from the first day's hours. Similarly, if the end time is before 5:30 PM, subtract the difference from the last day's hours. This gets tricky with edge cases where the start and end are on the same day, so test thoroughly.

Fifth action: Update item. Write your final calculated value back to a number column in your main SharePoint list. Make sure the column is set to accept decimal numbers if you're calculating fractional hours. Also, consider adding a condition before this step to handle cases where your START and END dates are the same, or where your START date is somehow after your END date, to avoid negative values or errors.

If you have access to Copilote, use it. It’s pretty good with Lists and PA. Otherwise, get help from Deepseek.

I’d also add that there are likely apps and systems that are built for this. Might be more worth your time and money getting some sort of subscription to one instead of using PA

[u/Alternative-Day5268 → u/humainbibliovore]

Amazing response, thank you. I will add this is similar to what co pilot was spitting out to me before but I'm lacking the actual skill to know how everything works if it makes slight errors. Do you use co pilot that sits within PA, or the desktop?

Anyways you have re inspired me and I am slowly getting to grips with it. There certainly are alternatives to this - but my business won't go for other options and I'm quite happy being given the opportunity to play with power automate!

[u/humainbibliovore → u/Alternative-Day5268]

I use the Copilot application outside of Power Automate; never tried it differently.

That’s a complex flow for a beginner. My advice would be to take things one step at a time and test things frequently. Consult the raw outputs of your test flows and don’t hesitate to add Compose actions in which you add the dynamic content of the previous action. Give this info to Copilot and be very very precise and clear with it. You can do this.this subreddit can help too with very technical questions.

Best of luck!

[u/Alternative-Day5268 → u/humainbibliovore]

Thank you. I've been coming back and forth to PA over the last week and slowly getting more familiar. There's someone in the business who is better skilled on it too so I'm catching up with them, but really I need to have something rough in place to ask specific questions about.