cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LMeijdam Established Member
Established Member

Time to integer, and vice versa

Hi all,

 

I have a report where I want a measure that returns the amount of seconds, to be converted to "HH:MMSmiley FrustratedS". In the same report for another measure I want a "HH:MMSmiley FrustratedS" column to be converted to the ammount of seconds in it as a integer. 

 

Best regards,

L.Meijdam

1 ACCEPTED SOLUTION

Accepted Solutions
DAX0110 Member
Member

Re: Time to integer, and vice versa

Seconds number to Time string:

      =FORMAT( [seconds] / 60 / 60 /24, "hh:nn:ss" )

 

Time string to Seconds number:

      =TIMEVALUE( [time str] ) * 24 * 60 * 60

 

 

6 REPLIES 6
DAX0110 Member
Member

Re: Time to integer, and vice versa

Seconds number to Time string:

      =FORMAT( [seconds] / 60 / 60 /24, "hh:nn:ss" )

 

Time string to Seconds number:

      =TIMEVALUE( [time str] ) * 24 * 60 * 60

 

 

LMeijdam Established Member
Established Member

Re: Time to integer, and vice versa

Hi @DAX0110,

 

The "string to seconds number" is working like expected thankyou very much!

 

The other calculation is giving me a error, although I don't think the formula is incorrect. I want a calculated column that is in "Whole number" format to change the amount of seconds to a time value. But when I use your formula it returns: Cannot convert value '' of type Text to type Date. And I don't really understand why since it is in "Whole number" format.

 

Capture.PNGMy calculated column

Best regards,

L.Meijdam

DAX0110 Member
Member

Re: Time to integer, and vice versa

Mmm, that's interesting.  In my test workbook the Power Pivot "seconds" column is "decimal" type with "general" format.

 

If you feed the output column from "string to seconds number" into the "seconds number to string" formula, it should work (I did that to double check my formula).

 

 

LMeijdam Established Member
Established Member

Re: Time to integer, and vice versa

Hi @DAX0110,

 

yes I tried that, that is returning the expected outcome. It just does'nt work when I insert my other calculated column.. Anyone have an idea how to fix this ?

 

Best Regards,

L.Meijdam

Community Support Team
Community Support Team

Re: Time to integer, and vice versa

Hi @LMeijdam,

 

I'd like to suggest you take a look at below blog which told about convert duration and time.

Aggregating Duration/Time

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
LMeijdam Established Member
Established Member

Re: Time to integer, and vice versa

Hi,

 

How I fixed it in the end was to SUM my calculated column by creating a measure, and after that using the "Seconds string to time" formula provided by @DAX0110 on that measure.

 

I have read the article @v-shex-msft linked and I learned some new things for it !

 

Thankyou all Smiley Happy

 

Best regards,

L.Meijdam