Can you let us know how the output result you are expecting. Few examples of output helps us to understand this better.
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 4-digit number field
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.
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
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
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 it is very close, unfortunately I am not able to open the workbook as my Tableau is not the same version.
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.
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)
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.
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+)'))
Appreciate your help.
I'm glad I could help