Time registration as 330 while 03:30:00 is needed

[Martijn2]

Hello all,

Anyone tips on how I can get this timestamp to for example 03:30:00?

101 = 1:01

10:10 = 1010

[AlienSx]

(x) => Time.FromText(Text.PadStart(x, 4, "0"), [Format = "hhmm"])

[saurabhtd]

@Martijn2 You can achieve this by adding custom column in table. Use below formula for creating column timestamp format. 

= if Text.Length([Tijd]) = 3 then 
    Text.PadStart(Text.Left([Tijd],1), 2, "0") & ":" & Text.Right([Tijd],2) & ":00" 
else 
    Text.Left([Tijd],2) & ":" & Text.Right([Tijd],2) & ":00"

[BeaBF]

@Martijn2 Hi!

Add a custom column with this code:

Time.FromText(Text.Start(Text.PadStart(Text.From([Tijd]), 4, "0"), 2) & ":" & Text.End(Text.PadStart(Text.From([Tijd]), 4, "0"), 2) & ":00")

please accept the answer as solution if it's ok!

BBF

[Martijn2]

This one works well, but only facing the following issues;

If it is 1:00 its described as 1. But the systems gets it as 00:01:00

[BeaBF · Accepted solution]

@Martijn2 ok, try to put this code in a custom column:

let
TidAsText = Text.From([Tijd]),
TidLength = Text.Length(TidAsText),
Result =
if TidLength = 1 then
Time.FromText("0" & TidAsText & ":00:00")
else if TidLength = 2 then
Time.FromText("00:" & TidAsText & ":00")
else if TidLength = 3 then
Time.FromText("0" & Text.Start(TidAsText, 1) & ":" & Text.End(TidAsText, 2) & ":00")
else
Time.FromText(Text.Start(TidAsText, 2) & ":" & Text.End(TidAsText, 2) & ":00")
in
Result

BBF

[shafiz_p]

Hi @Martijn2 
First change the column type from text to whole number.

Try below code in power query custom column:

=Text.PadStart(Text.From(Number.RoundDown([AB Tijd] / 100)), 2, "0") & ":" & Text.PadStart(Text.From(Number.Mod([AB Tijd], 100)), 2, "0") & ":00"

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz