False Time-Format

joslaz

Board Regular
Joined
May 24, 2018
Messages
76
Hello Community!


Slowly I despair with Access totally. I thought it was similar to Excel, but somehow I can not go on at all.


I have a query that has the time as text in the following format: "hhnnmm"


23000 (for 02:03:30)
3000 (for 00:30:00)
223000 (for 22:30:00)
240000 and 0 (each for 00:00:00)


Once at the field tBegin and tEnd.
I would now like to get the duration or difference, so tEnd-tBegin.


It is not that trivial, as it is in Excel.
How can I somehow solve this?


Greeting!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
As far as I can see none of those values are 'real' time values, in Access or Excel, and you would need to convert them to get a duration/time difference.
 

joslaz

Board Regular
Joined
May 24, 2018
Messages
76
Yes, thats right.
Unfortunatelly, its text.

Does anyone knows, how to convert for a time value?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
23000 (for 02:03:30) - how do you get it? probably it should be 0.02:30:00 or too much zeroes in source
3000 (for 00:30:00)
223000 (for 22:30:00)
240000 and 0 (each for 00:00:00)

duration format is d.hh.mm.ss

Column1Duration
23000​
0.02:30:00​
3000​
0.00:30:00​
223000​
0.22:30:00​
240000​
0.00:00:00​
 
Last edited:

joslaz

Board Regular
Joined
May 24, 2018
Messages
76

ADVERTISEMENT

Thats right. Its 0.02:30:00 for 23000.

Thanks!
And how can I implement this format?

It's not the original table, it's just a query.
I can't change the type for the fields.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
you can try PowerQuery (Excel 2010 and higher)
but you need to find algorithm for all values, if not this is most of hand job

this is M code for table above.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Length" = Table.AddColumn(#"Changed Type", "Length", each Text.Length([Column1]), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Text Length", "Custom", each if [Length] = 4 then [Column1] else null),
    #"Added Prefix" = Table.TransformColumns(#"Added Conditional Column", {{"Custom", each "00" & _, type text}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Prefix", "Custom.1", each if [Length] = 5 then [Column1] else null),
    #"Added Prefix1" = Table.TransformColumns(#"Added Conditional Column1", {{"Custom.1", each "0" & _, type text}}),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Prefix1", "Custom.2", each if [Length] = 6 then [Column1] else null),
    #"Merged Columns" = Table.CombineColumns(#"Added Conditional Column2",{"Custom", "Custom.1", "Custom.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Column1", "Length"}),
    #"Split Column by Position" = Table.SplitColumn(#"Removed Columns", "Merged", Splitter.SplitTextByRepeatedLengths(2), {"Merged.1", "Merged.2", "Merged.3"}),
    #"Added Prefix2" = Table.TransformColumns(#"Split Column by Position", {{"Merged.2", each ":" & _, type text}}),
    #"Added Prefix3" = Table.TransformColumns(#"Added Prefix2", {{"Merged.3", each ":" & _, type text}}),
    #"Merged Columns1" = Table.CombineColumns(#"Added Prefix3",{"Merged.1", "Merged.2", "Merged.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged", type duration}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Merged", #duration(0, 0, 0, 0)}}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors",{{"Merged", "Duration"}})
in
    #"Renamed Columns"

maybe any VBA will be better solution?
 
Last edited:

joslaz

Board Regular
Joined
May 24, 2018
Messages
76

ADVERTISEMENT

Ok cool, thanks! This will fix the problem temporally

But unfortunately, I dont look for a PowerQuery Approach.
I need a solution in Access.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
I didn't notice ACCESS in the first post but ok I overlooked Thread is in Access part:) VBA should work in Access also ;)
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
One possibility:

first, a query to get a consisent 6-character string for every entry:

Query1:
Code:
SELECT Right(("000000" & [Column1]),6) AS Exp1
FROM Table1;

Then a query to parse the string into a time:

Query2:
Code:
SELECT TimeSerial(Mid([Exp1],1,2),Mid([exp1],3,2),Mid([Exp1],5,2)) AS Exp2
FROM Query1;

This may not handle 24:00:00 as you like. My example treats it as Timeserial(24,0,0) which evaluates to 1. That is correctly a full day, but is also exactly a time boundary (an instant in time that marks the very end of one day and the very start of another day). Anyway, MSAccess won't display it as 00:00:00 and internally stores 24:00:00 as one, not zero.
 

Forum statistics

Threads
1,136,434
Messages
5,675,846
Members
419,586
Latest member
RoteichA

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
Top