Difference between time

[joslaz]

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!

 

[Joe4]

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

 

[Joe4]

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.

 

[joslaz]

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!

 

[Joe4]

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?

 

[apr pillai]

? 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

 

[joslaz]

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?

 

[apr pillai]

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:

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.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

220300​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

223500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#CCFFCC"]

3200​

[/td][td="bgcolor:#CCFFCC"]A4: =--(TEXT(TEXT(A3, "00\:00\:00") - TEXT(A2, "00\:00\:00"), "hhmmss"))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td]Format of A4: General[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

223000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

234000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#CCFFCC"]

11000​

[/td][td][/td][/tr]
[/table]

 

[joslaz]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

220300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

223500​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD="bgcolor: #CCFFCC"]

3200​

[/TD]
[TD="bgcolor: #CCFFCC"]A4: =--(TEXT(TEXT(A3, "00\:00\:00") - TEXT(A2, "00\:00\:00"), "hhmmss"))[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD][/TD]
[TD]Format of A4: General[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

[/TD]
[TD]

223000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

8​

[/TD]
[TD]

234000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

9​

[/TD]
[TD="bgcolor: #CCFFCC"]

11000​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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