Calculate Business hours between 2 dates/times

exceluser20232410 

To calculate the total hours requested between two dates, considering Monday to Friday as working days with a working hour range of 9 AM to 5 PM, you can use the following formula in a calculated field in SharePoint:

=IF(AND(WEEKDAY([End Date])>1, WEEKDAY([Start Date])<=5),
(DATEDIF([Start Date], [End Date], "d")-1) * 8 +
(IF(HOUR([End Date])>=17, 8, HOUR([End Date])-9) - IF(HOUR([Start Date])<9, 0, HOUR([Start Date])-9)),
0)

Explanation of the formula:

1. The `WEEKDAY` function is used to check if the start and end dates are between Monday to Friday. A weekday value of 1 represents Sunday, and a value of 7 represents Saturday.
2. If the start date is between Monday to Friday (inclusive) and the end date is after Monday, then the calculation proceeds.
3. The `DATEDIF` function calculates the number of days between the start and end dates, excluding the end date.
4. The calculated number of days is multiplied by 8 (assuming 8 working hours per day).
5. The hours worked on the start and end dates are then added. If the end date is after 5 PM (17:00), 8 hours are added. If the start date is before 9 AM (09:00), no hours are added.
6. If the start or end dates fall on weekends or outside the working hour range, the result is set to 0.

Please note that SharePoint calculated fields don't update automatically, so you'll need to manually trigger a recalculation (e.g., by editing and saving the list item) to see the updated result in the calculated field.