Menu
I'll illustrate what I would like to get in the following example:
'2010-09-01 03:00:00' - '2010-09-01 00:10:00' Using TIMEDIFF(), we get 2 as a result. This means, it's not considering the 50 minutes left.
In this case, what I'd like to get is: 50 (minutes) / 60 = 0.83 period. Therefore, the result should be 2.83 and not 2.
863
To calculate the number of hours between two dates we can simply subtract the two values and multiply by 24.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
Another simple technique to calculate the duration between two times in Excel is using the TEXT function: Calculate hours between two times: =TEXT(B2-A2, "h") Return hours and minutes between 2 times: =TEXT(B2-A2, "h:mm") Return hours, minutes and seconds between 2 times: =TEXT(B2-A2, "h:mm:ss")
select time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600; +-----------------------------------------------------------------------------+ | time_to_sec(timediff('2010-09-01 03:00:00', '2010-09-01 00:10:00' )) / 3600 | +-----------------------------------------------------------------------------+ | 2.8333 | +-----------------------------------------------------------------------------+ If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
