False Time-Format

[joslaz]

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!

 

[Norie]

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]

Yes, thats right.
Unfortunatelly, its text.

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

 

[sandy666]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Column1[/td][td][/td][td]Duration[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

23000​

[/td][td][/td][td]

0.02:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3000​

[/td][td][/td][td]

0.00:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

223000​

[/td][td][/td][td]

0.22:30:00​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

240000​

[/td][td][/td][td]

0.00:00:00​

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

 

[joslaz]

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]

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.

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?

 

[joslaz]

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]

I didn't notice ACCESS in the first post but ok I overlooked Thread is in Access part VBA should work in Access also

 

[xenou]

One possibility:

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

Query1:

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

Then a query to parse the string into a time:

Query2:

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.

 

[apr pillai]

Check this post:https://www.mrexcel.com/forum/micro...nce-between-time-post5109052.html#post5109052