Mar 12 2021 11:57 PM - edited Mar 12 2021 11:58 PM
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)
Mar 13 2021 12:37 AM
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.
Mar 13 2021 02:30 AM - edited Mar 13 2021 02:37 AM
Mar 13 2021 02:30 AM - edited Mar 13 2021 02:37 AM
Solution
There are several methods but since you are using 24 Hrs Date Time stamp value the I would like to suggest few, shown below.
=Q24-Q23
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)
Mar 13 2021 02:30 AM - edited Mar 13 2021 02:37 AM
Mar 13 2021 02:30 AM - edited Mar 13 2021 02:37 AM
Solution
There are several methods but since you are using 24 Hrs Date Time stamp value the I would like to suggest few, shown below.
=Q24-Q23
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)