Forum Discussion

Martijn2's avatar
Martijn2
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

 

 

 

Need Help
Tips and Tricks
  • BeaBF's avatar
    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

6 Replies

  • shafiz_p's avatar
    shafiz_p
    Icon for Super User rankSuper User
    1 year ago

    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

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Martijn2
      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's avatar
        BeaBF
        Icon for Super User rankSuper 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

  • saurabhtd's avatar
    saurabhtd
    Icon for Resolver II rankResolver II
    1 year ago

    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"



  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    1 year ago
    (x) => Time.FromText(Text.PadStart(x, 4, "0"), [Format = "hhmm"])