SOLVED

# Formula to calculate response time in hours and minutes

Copper Contributor

# Formula to calculate response time in hours and minutes

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.

7 Replies

# Re: Formula to calculate response time in hours and minutes

The attached workbook contains a custom VBA function.

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

# Re: Formula to calculate response time in hours and minutes

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")))``````

# Re: Formula to calculate response time in hours and minutes

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.

# Re: Formula to calculate response time in hours and minutes

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.

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Formula to calculate response time in hours and minutes

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.

# Re: Formula to calculate response time in hours and minutes

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.

# Re: Formula to calculate response time in hours and minutes

Hi, Thanks for the help! It works now.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Formula to calculate response time in hours and minutes

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.