SOLVED

Formula for Hour Difference

Brass Contributor

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)

 

Sameer_Kuppanath_Sulta_0-1615621940545.png

 

4 Replies

@Sameer_Kuppanath_Sultan 

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.

 

S0199.png

best response confirmed by Sameer_Kuppanath_Sultan (Brass Contributor)
Solution

@Sameer_Kuppanath_Sultan 

 

There are several methods but since you are using 24 Hrs Date Time stamp value the I would like to suggest few, shown below.

 

Rajesh-S_0-1615630976985.png

 

  1. 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.

 

Glad to help you,, keep asking ☺
Ok thanks.
1 best response

Accepted Solutions
best response confirmed by Sameer_Kuppanath_Sultan (Brass Contributor)
Solution

@Sameer_Kuppanath_Sultan 

 

There are several methods but since you are using 24 Hrs Date Time stamp value the I would like to suggest few, shown below.

 

Rajesh-S_0-1615630976985.png

 

  1. 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.

 

View solution in original post