Difference between time

joslaz

Board Regular
Joined
May 24, 2018
Messages
76
Hey guys!

I have 2 columns with the following time-format:

Column 1: hhmmss (223000)
Column 2: hhmmss (234000)

Now, how can I get the difference between them?
It would be 50min (5000).

Do you have any suggestions?


best regards!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Column 1: hhmmss (223000)
Column 2: hhmmss (234000)

Now, how can I get the difference between them?
It would be 50min (5000).
Can you explain how you get 50 minutes?
To me, it looks like it should be 70 minutes (23 hours, 40 minutes minus 22 hours 30 minutes is 70 minutes).
 
Upvote 0
So, in the format you want, it would return 1 hour 10 minutes.
So, you would just do [Column2] - [Column1] and format the result as "hhnnss".

Note that all date/time fields have BOTH a date and time component. So even if you do not show the date, it is still there and factored into calculations.
So you need to be aware of that fact.
 
Upvote 0
Hey, thanks!

So, I have now the difference between the two times, but there is no Value/ result.
If i click on the cell, I get a random date. Is this what you mean?
How can I manage this?

for example:
Difference between 220300 - 223500 = 04.10.1908


Many thanks!
 
Upvote 0
I think that is because you are neglecting the date portion of these cells (they are always there, whether you make that part visible or not).
If you temporarily format your cells to show the date value along with the time value, what do those two cells show?
 
Upvote 0
Code:
? Format((234000-223000)/86400,"hh:nn:ss")

Result: 03:03:20

Whole numbers are considered as date numbers starting from 12/31/1899 =1

Today's date (07/20/2018) number is 43301. Today's date & and time together at 12.00 PM is 43301.5

Since, the time in seconds are in whole numbers you have to convert them into internal computer time value to get them correctly. After finding the difference between those two values convert it into internal time value: difference/86400. There are 86400 seconds in a day i.e. 24 * 60 * 60.

For more details visit the page:https://www.msaccesstips.com/2010/07/date-and-time-values.html

 
Last edited:
Upvote 0
I think that is because you are neglecting the date portion of these cells (they are always there, whether you make that part visible or not).
If you temporarily format your cells to show the date value along with the time value, what do those two cells show?

Thanks!

If I format the cell in the format ( "dd/mm/yyy hh:nn:ss") I got dd/mm/yyy hh:nn:ss as the value.
I am not sure, if the cell consists any time or date format.
Is it possible the cell ist only as a numeric value declared?




 
Upvote 0
Sorry, I overlooked the fact that the numbers are in hhmmss format.

Doesn't matter which way you use the format string you cannot get the correct value by subtracting one number from the other.

1. 23 hrs 40 min 00 seconds must be converted into seconds first.
2. Like-wise the first value 22 hrs 30 min 00 seconds convert into seconds.
3. Then find the difference between them in seconds.
4. Convert the difference into computer's internal representation of time value dividing 86400 into the difference. At step 2 & 3 you may convert seconds into internal time value, before finding the difference.
5. Format the value in "hh:nn:ss"

This you can easily do it in a function by passing both values as parameters. Sample Function is given below:

Code:
Public Function hhmmss(ByVal num1 As Long, ByVal num2 As Long) As String
Dim x As Double, y As Double, Z as long

Z = 86400

  x = Eval(Left(num1, 2) * 3600 + Mid(num1, 3, 2) * 60 + Right(num1, 2)) / Z

  y = Eval(Left(num2, 2) * 3600 + Mid(num2, 3, 2) * 60 + Right(num2, 2)) / Z


hhmmss = Format(y - x, "hh:nn:ss")


End Function

example:

? hhmmss(223000, 234000)

Result: 01:10:00


Call the function from the Query column or from TextBox on Form or from another function.

NB: The function presented here is the simplest form, without any checks on validity of values received in the function or other pitfalls which may occur due to unexpected errors. I have not tested the code properly, if any issues or clarification required please post it here. You may use the code on your own risk.
 
Last edited:
Upvote 0
A​
B​
2​
220300​
3​
223500​
4​
3200​
A4: =--(TEXT(TEXT(A3, "00\:00\:00") - TEXT(A2, "00\:00\:00"), "hhmmss"))
5​
Format of A4: General
6​
7​
223000​
8​
234000​
9​
11000​
 
Upvote 0
A​
B​
2​
220300​
3​
223500​
4​
3200​
A4: =--(TEXT(TEXT(A3, "00\:00\:00") - TEXT(A2, "00\:00\:00"), "hhmmss"))
5​
Format of A4: General
6​
7​
223000​
8​
234000​
9​
11000​

<tbody>
</tbody>

I am sorry, but what is that exactly?
I dont now how to implement this approach.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top