Formula to calculate response time in hours and minutes

[Lee_Ginny]

Hi Anyone can suggest the formula to calculate the total time use for a response.

Example below: Example 1: Start time: 5/1/2024, 16:19. End time: 5/2/2024, 10:39.

Example 2: start time 5/3/2024 9:21. End time: 5/3/2024, 9:24.

By considering working hour 8~5pm and holiday is on 5/1/2024.

[#2]

@Lee_Ginny 

The attached workbook contains a custom VBA function.

It uses a named range called Holidays that lists public holidays to exclude.

[#3]

@Lee_Ginny 

I tried it with a formula. Hans was quicker :)

=LET(hol,Public_holidays[List Public holidays],
IF(COUNT(A2:B2)<>2,"",TEXT(SUM(
IF(AND(INT(A2)=INT(B2),WEEKDAY(A2,2)<6,OR(INT(A2)=hol)=FALSE),MAX(0,B2-A2),0),
IF(AND(INT(A2)<>INT(B2),WEEKDAY(A2,2)<6,OR(INT(A2)=hol)=FALSE),MAX(0,17/24-(A2-(INT(A2)))),0),
IF(AND(INT(A2)<>INT(B2),WEEKDAY(B2,2)<6,OR(INT(B2)=hol)=FALSE),MAX(0,(B2-(INT(B2))-8/24)),0),
MAX(0,(NETWORKDAYS.INTL(A2,B2,1,hol)-1)*9/24)
),"[hh]:mm:ss")))

[#4]

@Hans Vogelaar 

May I know how can the function move to my workbook? As I try to edit the information from the workbook you shared, the formula will run.

Link 

[#5]

Hi,

I have tried the formula, but it seems not excluded weekend.
Example below: Start date: 2/2/2024, 3pm, End date: 2/5/2024, 8am. 2/3 & 2/4 is weekend.
But the results show 83hrs.

Besides, 

I will need 2sets of record total response time for Planning and ISR, but the formula does not work for the total response time in ISR. Need assist as well.

[#6 · Accepted solution]

@Lee_Ginny 

I have adjusted and corrected my function again. I think it should work now. But you would have to extend the list of public holidays for your country. Also, if you want to use this formula commercially, you would have to put it to the test with lots of examples.

[#7]

@Lee_Ginny 

Open both your workbook and my demo workbook.

Press Alt+F11 to activate the Visual Basic Editor.

In the Project Explorer pane on the left hand side, expand Modules under my workbook if necessary. You'll see Module1. Drag Module1 to your workbook and drop it there. This will copy the module with the code for the function to your workbook.

[#8]

Hi, Thanks for the help! It works now.