Pb with cell size in excel

#1

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

#2

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”.

image

#3

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.

#4

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

#5

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

#6

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],":"))

#7

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

#8

You can send us the project and its source data to support@easymorph.com, and I will see what doesn’t work.