Forum Discussion

Frequent Visitor

1 year ago

Solved

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

Hello all, Anyone tips on how I can get this timestamp to for example 03:30:00? 101 = 1:01 10:10 = 1010

Tips and Tricks

BeaBF
1 year ago
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

Icon for Super User rank

Super User

1 year ago

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

Frequent Visitor

1 year ago

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
Super User
1 year ago
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

Related content