Forum Discussion
Formula for Hour Difference
Hello All
What is the formula to find the time difference in hour format when it crosses the next day as shown below. I need the answer as 31 Hours 55 Mins (24 hours + 7:55 Mins)
There are several methods but since you are using 24 Hrs Date Time stamp value the I would like to suggest few, shown below.
- Formula in cell Q26:
=Q24-Q23- Apply [h]:mm:ss cell format on the cell.
- [h] or [H] considers 24 hrs clock.
2. Formula in cell Q28:
=TEXT(Q24-Q23,"[H]:MM")3. Now the formula which counts Days along with elapsed Hours, Minutes & Seconds in cell Q30.
=INT(Q24-Q23) & " days, " & HOUR(Q24-Q23) & " hours, " & MINUTE(Q24-Q23) & " minutes and " & SECOND(Q24-Q23) & " seconds"4. One more tricky formula is:
=IF(Q24>Q23,Q24-Q23,1-Q23+Q24)- Applied cell Format is [h]:mm:ss .
- Adjust cell references in the formula as needed.
4 Replies
- Glad to help you,, keep asking ☺
- Ok thanks.
There are several methods but since you are using 24 Hrs Date Time stamp value the I would like to suggest few, shown below.
- Formula in cell Q26:
=Q24-Q23- Apply [h]:mm:ss cell format on the cell.
- [h] or [H] considers 24 hrs clock.
2. Formula in cell Q28:
=TEXT(Q24-Q23,"[H]:MM")3. Now the formula which counts Days along with elapsed Hours, Minutes & Seconds in cell Q30.
=INT(Q24-Q23) & " days, " & HOUR(Q24-Q23) & " hours, " & MINUTE(Q24-Q23) & " minutes and " & SECOND(Q24-Q23) & " seconds"4. One more tricky formula is:
=IF(Q24>Q23,Q24-Q23,1-Q23+Q24)- Applied cell Format is [h]:mm:ss .
- Adjust cell references in the formula as needed.
Let's say the times are in A2 and B2. (By the way, isn't the "from" time on the left and the "to" time on the right?)
The formula for the difference is =B2-A2
Apply the custom number format [h]:mm to the cell with the formula.
