Forum Discussion

mcquerry-dillon's avatar
mcquerry-dillon
Regular Visitor
2 years ago
Solved

#Error Involving Time Conversion

Hello,

 

I am trying to convert an integer, for example 63148, which is the amount of seconds from midnight, into a time format such as '5:36PM'.

 

I've tried using combinations of changing the box properties to Time, using a mix of conversion functions, but I still end up with an #Err in my field. I tried looking this up and couldn't find a specific use case solution for what I'm trying to do.

 

Any help would be appreciated.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi mcquerry-dillon ,

    In the case of CDate expression, she can only convert a string of date type to a date, so if she converts the integer directly, an error will be reported.

    Below is my table:

    I create Hrs , Mins , Time column

    The following expression might work for you:

    Time =Fields!Hrs.Value & Format(Fields!Hrs.Value ,":")  & Format(Fields!Mins.Value)
Mins = Fields!NewColumn0.Value\60 - Fields!Hrs.Value*60
Hrs = Fields!NewColumn0.Value\3600
Time1 = CDate(Fields!Time.Value)

    The final output is shown in the following figure:

    Expression examples in Power BI Report Builder - Power BI | Microsoft Learn

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • gadielsolis's avatar
    gadielsolis
    Icon for Resolver III rankResolver III
    2 years ago

    Hello,

     

    I would recommend the following DAX:

    If this works please mark this message as solution.

    • mcquerry-dillon's avatar
      mcquerry-dillon
      Regular Visitor
      2 years ago

      hello, 

      I should clarify that I'm using the PowerBI Report Builder so this is what I'm trying to work with

       

      The op_time field value is an integer as mentioned earlier in seconds

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mcquerry-dillon ,

    In the case of CDate expression, she can only convert a string of date type to a date, so if she converts the integer directly, an error will be reported.

    Below is my table:

    I create Hrs , Mins , Time column

    The following expression might work for you:

    Time =Fields!Hrs.Value & Format(Fields!Hrs.Value ,":")  & Format(Fields!Mins.Value)
Mins = Fields!NewColumn0.Value\60 - Fields!Hrs.Value*60
Hrs = Fields!NewColumn0.Value\3600
Time1 = CDate(Fields!Time.Value)

    The final output is shown in the following figure:

    Expression examples in Power BI Report Builder - Power BI | Microsoft Learn

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.