Forum Discussion
Datevalue+timevalue returns "the cell currently being evaluated contains a constant"
Hello guys,
I would like to receive some help from you, as i cannot understand why my formula works for some values and for others isn't.
So, in date1 column, i have a formula applied to a previous column, which deletes CHAR160 from the previous column which cannot be seen here. Next, i copied all the values from date1 to column date2 as values in order to apply a further formula (although its the same result if i apply the next formula directly to the date1 column). The formula in date3 is - =Datevalue+timevalue for the same cell in date2 column.
And now comes the ugly part - for some of the cells the formula works perfectly, for others it returns the #value. I can't make any correlation between the #value ones because there are different years, different types and so on.
All the cells are formatted the same - the ones for which the formula works are formatted the same as the ones that the formula doesn't work for.
I've sent this spreadsheet to someone who has windows7 and the formula works with no flaws. For windows10 and excel16 - the errors happen.
Can anyone please help in this matter?
Thank you!
you made a mistake in the function argument separators.
after TEXT(H2 you used ;
in some machines the functions argument separators is either comma or semicolon.
so part of your formula uses comma and part of it semicolon which is not correct.
you can use only one type of separator depending on the regional language setting of your machine.
please see attached file that demonstrates that formula is working.
13 Replies
can you try with this
=IFERROR(DATEVALUE(B2)+TIMEVALUE(B2),TEXT(B2,"General"))
Not working :(
you made a mistake in the function argument separators.
after TEXT(H2 you used ;
in some machines the functions argument separators is either comma or semicolon.
so part of your formula uses comma and part of it semicolon which is not correct.
you can use only one type of separator depending on the regional language setting of your machine.
please see attached file that demonstrates that formula is working.
