How to convert decimal number to Days, Hours, Minutes, and Second

[Lafrank]

I have noticed several videos made attempts to convert from decimal to HH:MM: SS but almost none talk about DAYS.

And most are using data that already have a start date/time to end date/time.

Please, where you have just ONLY the decimal number. How do you convert it to DD:HH:MM: SS?

Thank you

[ppm1]

Please see this article for how to do this.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[ChrisMendoza · Accepted solution]

@Lafrank - I think what you'll find is that you have to build it and when you do you will also find that it will end in a string/text value if you want that look. If you're wanting to do math operations then you will need to have the parts that make up the look to do what is necessary.

I've attached the PBIX so you can see the math to build the string.

[ChrisMendoza]

Seconds = TableName[Minutes] * 60

DayValue = INT ( [Seconds] / 86400 )

HourValue = 
FORMAT (
    FLOOR (
        MOD ( [Seconds], 86400 ) / 3600,
        1
    ),
    "00"
)

MinuteValue = 
FORMAT (
    MOD ( [Seconds], 3600 ) / 60,
    "00"
)

Formatted as text = COMBINEVALUES ( ":", TableName[DayValue], TableName[HourValue], TableName[MinuteValue], "00" )

[Anonymous]

do you have an example of the data you're trying to convert?

Date/Time representations can be represented as serial numbers for whole dates, and decimal representations are time intervals during the day.

If you change a column that has decimal values (e.g. 41974.12) you should be able to change this to DateTime just by changing the data type.

It's important that the column type is not Text and is Decimal otherwise it cannot handle the change.

In my image below, The first 3 columns were all stored as Decimal. I changed [DateTime] to data type Date/Time, and changed [Date] to data type Date. This auto updated appropriately. I also created [Date Time] by using a DAX formula.

All of them are being handled appropriately by PowerBI