Convert decimal days to days:hrs:min

[icdns]

Hello, 

Need your help in converting my decimal days to this format ( Days Hours Min) 

is it possible? 

examples:

- 1.5 would be "1 day 12 hours" 
- 0.25 would be "0 day 6 hours" 

Thank you! 🙂 

[Megha166]

Here is the formulae for Date in Days and Hours. The same can be replicated for Minutes also.

Date in Text =

VAR IntegerPart= Trunc('Item'[Decimal Date])

VAR DecimalPart= RIGHT('Item'[Decimal Date],LEN('Item'[Decimal Date])-SEARCH(".",'Item'[Decimal Date]))

VAR Days = CONCATENATE(IntegerPart," Days")

VAR Hours = CONCATENATE( DIVIDE(DecimalPart, 10) * 24, " Hours")

Return CONCATENATE(Days,Hours)

[icdns]

Hello @Megha166 ,

I tried creating a column, but i encountered below error: what does it mean? 🙂 

Thank you! 

[Megha166]

This is the complete solution with Minutes code also. ANd its working fone for me. Above is the result screenshot. What is the datatype of the Date Column? OR Can you share the screenshot of the Data and the error?

---

@icdns wrote:

Hello @Megha166 ,

I tried creating a column, but i encountered below error: what does it mean? 🙂 

Thank you! 

---

[icdns]

Hello, @Megha166 ,

My decimal date is in decimal data type 🙂  is it also possible? 

Thank you! 

[Megha166]

Please send the screenshot or check if above screenshot helps? Basically the error that you are getting says SEARCh is not able to find "."  in the date. My code is finding "." in the date and then separating it to Integer and Decimal Part.

[icdns]

Hi @Megha166 ,

I am getting this error. below is the screenshot:

my MAX COMPLAINT PROCESSING DAYS is in DECIMAL FORMAT. 

Thank you! 🙂 

[icdns]

Hi @Megha166 , 

Ohh i see.. this is the sample data: 

It's kinda weird.. it also have a "." 

[Megha166]

I see there are so many values as 0(WITHOUT any decimal). These values are throwing the error. Give me sometime. Let me handle these scenarios also in the DAX code. 

Can you also give me the expected output of the sample Dates? Would help me test the code.

[icdns]

Thank you so much!!! @Megha166 

[Megha166 · Accepted solution]

@icdns 

Date in Text2 =

VAR DecimalDateCheck= IF(CONTAINSSTRING('Item'[Decimal Date],"."),"Y","N")

VAR DateIntegerPart= TRUNC('Item'[Decimal Date]) //1

VAR DateDecimalPart= RIGHT('Item'[Decimal Date],LEN('Item'[Decimal Date])-SEARCH(".",'Item'[Decimal Date],,0))//2

VAR Days = CONCATENATE(DateIntegerPart,IF(DateIntegerPart = 1, " Day ", " Days ")) //1 Day

VAR DecimalHours = DIVIDE(DateDecimalPart, 10) * 24 //4.8

VAR DecimalHourCheck= IF(CONTAINSSTRING(DecimalHours,"."),"Y","N")

VAR Hours = CONCATENATE( TRUNC(DecimalHours), " Hours ") //4

VAR HourDecimalPart = RIGHT(DecimalHours,LEN(DecimalHours)-SEARCH(".",DecimalHours,,0))//8

VAR Minutes = CONCATENATE( TRUNC(DIVIDE(HourDecimalPart, 10) * 60), " Minutes") //48

Return IF(DecimalDateCheck="N",CONCATENATE('Item'[Decimal Date]," Days"),IF(DecimalHourCheck="N", CONCATENATE(Days,Hours),CONCATENATE(CONCATENATE(Days,Hours),Minutes)))

Result Screenshot Here:

[icdns]

Hi @Megha166 , 

Wow this is great! I have tried the logic (Date in Text 2) column.. but highlighted occurs..

by the way the max the MAX_FORMAT is the correct one. can I convert the 9967222222223 to just 9 hrs? 🙂 

Thank you so much!! Sorry i am very new to powerbi

[Megha166]

@icdns 

Please give me the exact input Date that is giving this big 9999***** hours. Also, please Give Kudos to replies if they are helping you 🙂. That's the thumbsup sign below my replies.

[icdns]

Thanks alot for the help!! 😄 For the highlighted one 9996xxxxxxx , it came from measure (max_complaint_processing_date): 

[Megha166]

@icdns 

You need to give the complete calculation if thats the input Date. Upper Code would just truncate to get first character of the Hours(Here 9).

[Megha166]

Mark it Solved if this solves your question.

[icdns]

Will try to explore more on this. Really a big help! 

Thank you! @Megha166 

[Megha166]

No Problem. Post the problem again if you still find some issue. Tag Me too. 🙂

[icdns]

Hi @Megha166 , 

Is your decimal days in Decimal data type? 

[Megha166]

Can you give me the Exact Date that you are using?