Hi everybody,
I have a problem with a file in which the size of a cell is set like this :
146:00:00 but il you put your mouse on the cell, you can see the real size : 06/01/1900 (space) 02:00:00
My question is : how can I transform this format in number like 146,00 ?
I want to calculate thereafter tne number of extra hour between.
Thank you for your help.
Karine
Hi Karine,
just to make sure that I understood you correctly:
You have a value 146:00:00 which is converted into a date/time. But you need it to be a number 146, correct?
I would use the asnumber function, for instance:
asnumber(keepbefore("146:00:00", ":"))
which results in 146. The keepbefore function keeps only characters before 1st encounter of “:”.
Also make sure that the column format is set to “No format”.
Hi Dgudkov,
It means right and the good solution but how can I apply this function to all of my column without specifiy a number ?
I explain myself : I have this number in a column whose name is “Real Worked hours”. Each cells have differents numbers. And I want to transform all this column as you do in your example.
How can I do this ?
Thank you for your help.
Hi Karine,
I think that in the following formula :
asnumber(keepbefore(“146:00:00”, “:”))
you should replace “146:00:00” by the name of your column : [Real Worked hours]
asnumber(keepbefore([Real Worked hours], “:”))
Is it OK now ?
Best regards,
Michel
Hi Michel,
It doesn’t work… what a pity … it seams so easy.
asnumber(keepbefore("[Heures Réelles]",":"))-> this is the formula I use. And the result is "Can’t convert “Heures Réelles” to number.
Another solution or suggestion ?
Thank you for your help
Best regards
Karine
Karine,
field names should be wrapped only in square brackets. Double quotes around [Heures Réelles]
are not necessary. Try this expression:
asnumber(keepbefore([Heures Réelles],":"))
Thank you but it doesn’t work.
For the moment I’ve made a formula in excel before integrating my data in easymorph. It works but I find this is not an effective solution.
I need finding something to avoid doing some “do it yourself” because I think it is the better mind doing mistakes.
I continue searching.
Thank you.
Best regards
Karine
You can send us the project and its source data to support@easymorph.com, and I will see what doesn't work.