
1. Re: Convert [h]:mm in excel cell into decimal number in Tableau
ra.sae.0 Oct 11, 2016 11:36 PM (in response to Maziiar Abadi)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
praveen p Oct 12, 2016 1:26 AM (in response to Maziiar Abadi)Hi Maz,
Please try the below formula and check if it works for you
dateadd('minute',INT(RIGHT(STR([Header 1]),2)),dateadd('hour',INT(LEFT(STR([Header 1]),2)),TODAY()))
Replace Today() with the actual date you have in your database
The above formula works if its always in HH:MM 4digit number field
Thanks

3. Re: Convert [h]:mm in excel cell into decimal number in Tableau
Mahfooj Khan Oct 12, 2016 1:46 AM (in response to Maziiar Abadi)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

8 hours 9.3.twbx 54.9 KB


4. Re: Convert [h]:mm in excel cell into decimal number in Tableau
Hima vardhan Reddy Pavuluri Oct 12, 2016 1:53 AM (in response to ra.sae.0)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,position1) 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
Maziiar Abadi Oct 12, 2016 4:37 PM (in response to ra.sae.0)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
Maziiar Abadi Oct 12, 2016 4:39 PM (in response to praveen p)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
Maziiar Abadi Oct 12, 2016 4:38 PM (in response to Mahfooj Khan)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
Maziiar Abadi Oct 12, 2016 4:38 PM (in response to Hima vardhan Reddy Pavuluri)Hi Hima,
Thanks for the reply.
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
Mahfooj Khan Oct 12, 2016 9:25 PM (in response to Maziiar Abadi)4 of 4 people found this helpful 
10. Re: Convert [h]:mm in excel cell into decimal number in Tableau
Maziiar Abadi Oct 13, 2016 1:17 AM (in response to Mahfooj Khan)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
Mahfooj Khan Oct 13, 2016 2:17 AM (in response to Maziiar Abadi)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
Jaya Sai Oct 13, 2016 3:42 AM (in response to Maziiar Abadi)You can use these 3 calc fields to get your output.
1) INT(MID([Header 1],1,LEN(REPLACE([Header 1],":"," "))))*60 + RIGHT([Header 1],2)
2) INT(SPLIT([Header 1],':',1))*60+INT(SPLIT([Header 1],':',2))
3)int(REGEXP_EXTRACT([Header 1],'(\d+)')) + int(REGEXP_EXTRACT([Header 1],'\:+(\d+)'))

13. Re: Convert [h]:mm in excel cell into decimal number in Tableau
Maziiar Abadi Oct 13, 2016 5:07 PM (in response to Mahfooj Khan)Good point.
Appreciate your help.

14. Re: Convert [h]:mm in excel cell into decimal number in Tableau
Mahfooj Khan Oct 13, 2016 9:44 PM (in response to Maziiar Abadi)I'm glad I could help