6 Replies Latest reply on Jul 25, 2016 8:31 AM by Ashish Chaudhari

Convert DD:HH:MM string to whole number

Morning, all.

My data extract includes a string that shows a duration of time (DD:HH:MM). I would like to be able to turn this number into an amount of hours, where 1:04:30 would equal 28.5 hours.

Can anyone advise on this? See "Completion Time" in the attached workbook.

• 1. Re: Convert DD:HH:MM string to whole number

Hey Rachel,

(INT(LEFT([Completion Time (DD:HH:MM)], 2)) * 24) +

INT(LEFT(RIGHT([Completion Time (DD:HH:MM)], 5), 2)) +

(INT(RIGHT([Completion Time (DD:HH:MM)], 2))/60)

**Edit, I just realized you have some entries that only have one digit for the days. Pad these to be two digits with a leading zero using the below formula**

IF LEN([Completion Time (DD:HH:MM)]) = 7

THEN '0' + [Completion Time (DD:HH:MM)]

ELSE [Completion Time (DD:HH:MM)]

END

• 2. Re: Convert DD:HH:MM string to whole number

You could setup a calculation

Hours

[Get_Days]*24+[Get_Hours]*1+[Get_Minutes]/60

• 3. Re: Convert DD:HH:MM string to whole number

Hi Rachel,

I first converted the date field to a dimension to then split it into 3 new fields, one for the day, the other for the hours and minutes.

I then converted theses new fields to measures and created a calc field to add them up:

([Completion Time (day)]*24)+[Completion Time (hour) - Split 2]+([Completion Time (minutes) - Split 3]/60)

• 4. Re: Convert DD:HH:MM string to whole number

It bascially do step by step what Derrick suggested.

• 5. Re: Convert DD:HH:MM string to whole number

Rachel you can do this in number of ways using string functions. Like Left, Right, Split functions.

Regards,

Swarup

• 6. Re: Convert DD:HH:MM string to whole number

Hi Rachel,

Please find the below screenshot and confirm the output.

Calcs are as below

Days

INT(LEFT([Completion Time (DD:HH:MM)],int(FINDNTH([Completion Time (DD:HH:MM)],':',1))-1))

HRS

INT(RIGHT(LEFT([Completion Time (DD:HH:MM)],int(FINDNTH([Completion Time (DD:HH:MM)],':',2))-1),2))

Mins

INT(RIGHT([Completion Time (DD:HH:MM)],2))

Total Time

(([Days]*24*60)+([HRS]*60)+[Mins])/60

I hope this helps.

Thanks and Regards,

Ashish Chaudhari