14 Replies Latest reply on Oct 13, 2016 9:44 PM by Mahfooj Khan

# Convert [h]:mm in excel cell into decimal number in Tableau

I have an excel spread sheet and need to create a report based on that in Tableau. Below is example of my data set:

 19:15
 250:29
 74:24
 3:40
 0:14
 -36:27
 1953:29
 19:15
 253:38
 539:51

Which are [h]:mm and needs to be converted to workdays (8 hours).

Could anyone help me how to do that without manual formatting in excel.

• ###### 1. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Maz,

Can you let us know how the output result you are expecting. Few examples of output helps us to understand this better.

Thank you.

• ###### 2. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Maz,

Please try the below formula and check if it works for you

Replace Today() with the actual date you have in your database

The above formula works if its always in HH:MM 4-digit number field

Thanks

• ###### 3. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Maz,

Is that what you wanted? I'm assuming 8 hours equivalent to one day. workbook 9.3 attached for your reference. Feel free to ask If you've any query.

Mahfooj

• ###### 4. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Let me explain what i understand and what i can think of it as of now.

let's take one value as input for the sake of better understanding.

input=16:40

this input row from excel means 16 hours 40 minutes.

so first convert it into string using str function. Then lets divide it into 2 parts as hours and minutes,using find function find the position of ':' find(input,':')=3 .

find(str(input),':') this calculation will give you the position of ':' , 3 in our example. save this as positions calculation.

now in other calculation take like this . mid(input,0,position-1) this will give you hours , 16 in our example save it as hours.

next mid(input,position+1,len(input)) this will give you minutes , 40 in our example save it as minutes.

next take one calculation like this hours*60+minutes so this will give you total time in minutes and dived it by your working time that is 8*60 (8 hours ) minutes.

Finally you will get the decimal value that is number of working days with each working day as 8 hours.

16:40 ----- > 16*60+40= 960+40 =1000

so 1000 minutes is our time. now divide it by 8*60

1000/(8*60) ===> 2.08

so our working days is 2.08

• ###### 5. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Rasa,

Result should be something like:

 2.66 34.55 10.26 0.51 0.03 -5.03 269.45 2.66 34.98 18.58 74.46
• ###### 6. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Parveen,

Thanks for the reply. The issue is as the data is total hours and minutes so they are not standard hh:mm format that means as you can see we could have

 1953:29 (1953 hours + 29 minutes)

which in that case I should be able to convert that to business days based on say 8 hours a day.

Hope it helps.

Thanks,

Maz

• ###### 7. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Mahfooj,

Thanks it is very close, unfortunately I am not able to open the workbook as my Tableau is not the same version.

• ###### 8. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Hi Hima,

The issue with the data is it is not fixed hh:mm format. Sometimes is but sometimes is hhh:mm or hhhh:mm cause they are total hours and minutes and not normal day hour:minute.

• ###### 9. Re: Convert [h]:mm in excel cell into decimal number in Tableau
4 of 4 people found this helpful
• ###### 10. Re: Convert [h]:mm in excel cell into decimal number in Tableau

Awesome Mahfooj!

If I want to round it then add round at the top with decimal?

Round((INT(MID([Leave Pro Rata Balance (Hours)],1,FIND([Leave Pro Rata Balance (Hours)],":")-1))+

(INT(MID([Leave Pro Rata Balance (Hours)],FIND([Leave Pro Rata Balance (Hours)],":")+1,

LEN([Leave Pro Rata Balance (Hours)])))/60))/8 , 2)

Sounds ok?

• ###### 11. Re: Convert [h]:mm in excel cell into decimal number in Tableau

You can do but I would suggest, use the inbuilt feature of tableau to change the format of your measure.

Additional function to round of the values doesn't make sense here, unnecessary it will put load on calculation and performance. You can format the values like this. Just right click on your measure from the dimension/measures pane then go to default properties and select the format as per your requirement.  Hope this help.

Mahfooj

• ###### 12. Re: Convert [h]:mm in excel cell into decimal number in Tableau

You can use these 3 calc fields to get your output.