26

I have two time-formatted cells and I need to calculate the difference (e.g., 2.75) in hours between the two.

10:30 AM   |   1:15 PM

Simply 

=B1-A1

will not do the trick.

Improve this question
1

4 Answers 4

Reset to default
33

I would use the following formula to calculate the difference in hours.

Formula

=(B1-A1)*24

Explained

The time difference is calculated in days. If you multiply times 24, you end up with hours. Time difference is 2 hours and 3 quarters of an hour (165 min).

Screenshot

enter image description here

Example

I've prepared an example file for you: Time Tracker formula and included your own solution in C1.

Improve this answer
0
12

To calculate the time difference in hours between

10:30 AM   |   1:15 PM

Use the formula:

=B1-A1

and format that cell as Duration

2:45:00
Improve this answer
1
  • 1
    Format => Number => Duration
    – mshakeel
    Aug 30, 2017 at 7:02
1

In Google Sheets, I used the formula

=Round((hour(A2-A1)*60 + minute(A2-A1))/60,2)

to give me the difference in decimal hours.

Improve this answer
-1

I've created this formula, it might help somebody else.

=iferror(if((minute(E3) - minute(D3))>=0;hour(E3)-hour(D3) + (minute(E3) - minute(D3))/60;hour(E3)-hour(D3) + 1 + (60 - minute(E3) - minute(D3))/60))
Improve this answer
3
  • What's the time difference you expect?
    – Jacob Jan Tuinstra
    Apr 24, 2014 at 11:22
  • This just gets the time in hours. I'm using it in a time-tracking sheet for billing purposes.
    – Zlatko
    Apr 24, 2014 at 11:42
  • 2
    Yeah I understand, but what is the time difference between 10:30 AM and 1:15 PM?
    – Jacob Jan Tuinstra
    Apr 24, 2014 at 11:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.