Forum Discussion
Convert text value to time value I get error
Hi Anonymous ,
Based on your description, you can try to get the values of hours and minutes and convert them to the "whole number" data type, and then use the "IF" function to get the results you want. You can refer to this below:
1. Get the values of hours and minutes
hour = LEFT('Table'[Column1],2) minute = RIGHT('Table'[Column1],2)2. Convert to the "whole number" data type
3. Use "IF" function
time = IF('Table'[hour]<24&&'Table'[minute]<60,CONCATENATE('Table'[hour]&":",'Table'[minute]),"23:59:59")Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've tried duration too but it doesnt work. Can I somehow convert the "error" message that shows blank column in the reports to just say "23:59:59" or something instead?
Hi Anonymous ,
Based on your description, you can try to get the values of hours and minutes and convert them to the "whole number" data type, and then use the "IF" function to get the results you want. You can refer to this below:
1. Get the values of hours and minutes
hour = LEFT('Table'[Column1],2)
minute = RIGHT('Table'[Column1],2)2. Convert to the "whole number" data type
3. Use "IF" function
time = IF('Table'[hour]<24&&'Table'[minute]<60,CONCATENATE('Table'[hour]&":",'Table'[minute]),"23:59:59")
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! That worked perfectly 🙂
Hello again! I got a second question now, i've made so I got seconds too now "hh:mm:ss". But I cant seem to extract it like in the solution above. I cant go above 2 steps in either right or left because it cant extract the ":" 😞 It says "Cannot convert value '01:01' of type Text to type Integer." Any idees?
Inställelstid=the text value I want to convert to time
Inställelsetiduträkning=the converted with RIGHT/LEFT columns.
But as you see the converted column has the seconds in minutes and so on.