Forum Discussion

Harreid's avatar
Harreid
Copper Contributor
Nov 30, 2019

Time formula

Can anybody please give me the formula to make this work. I have tried for many hours and just can't get it. Thank you for any help.   DATE TIME COME TIME GO HOURS WORKED 1     0:00:00 ...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 30, 2019

Harreid 

 

Alternatively:

Suppose your start and end times are in A1 and B1, then in C1:

 

 

=IF(A1>B1,1-A1+B1,B1-A1)

 

 

Also important to enter times so that Excel knows if it is e.g. 11:30 AM or 11:30 PM (i.e. 23:30). In your example, the formula probably did 7:30 (AM) minus 11:30 (AM). That's a negative and your result will ######### if formatted as a time.

 

 

 

 

  • Harreid's avatar
    Harreid
    Copper Contributor
    Dec 01, 2019

    Riny_van_Eekelen 

     

    Yes, it works, thank you, just the total does not work, any idea? OK, I got the total to work. Thank you very much. I'll buy you a beer if you ever come to my bar in Thailand. Cheers.

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Dec 01, 2019

      Harreid 

      Glad you solved the total yourself. I'll let you know when I'm near 🙂

      • Harreid's avatar
        Harreid
        Copper Contributor
        Dec 01, 2019

        Riny_van_Eekelen Hi again, well I thought I found the formula and got the right answer, but then it disappeared. Now I can't get it to work again. Do you come to Thailand?