Forum Discussion

Lee_Ginny's avatar
Lee_Ginny
Copper Contributor
May 09, 2024
Solved

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.

excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    May 10, 2024

    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 Replies

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    May 09, 2024

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

     

     

     

    • Lee_Ginny's avatar
      Lee_Ginny
      Copper Contributor
      May 10, 2024

      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.

       

       

       





      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        May 10, 2024

        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.

         

         

    • Lee_Ginny's avatar
      Lee_Ginny
      Copper Contributor
      May 10, 2024

      HansVogelaar 

       

      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.

       

      https://efinix-my.sharepoint.com/:x:/g/personal/zllee_efinixinc_com/EQOsCmvQKwROka96r_hn0y0Bzyv3fJDaFR4vx5Z2HFagdQ?e=6T2Yyn 

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 10, 2024

        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.

Resources