Datevalue+timevalue returns "the cell currently being evaluated contains a constant"

[Draghici Sorin]

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!

[#2]

can you try with this

=IFERROR(DATEVALUE(B2)+TIMEVALUE(B2),TEXT(B2,"General"))

[#3]

Not working :(

[#4 · Accepted solution]

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.

[#5]

Thank you very much for solving this one. I really appreciate it. I have used the ";" separator and now the formula works for those that were showing the #value error.

Now - a new "issue" occurs - as i have attached the photos, although all the cells are formatted with the same date type, as you can see it shows different formats for my dates. Therefore, when i hit the dropdown list in order to filter by months/years whatever, it shows the years, if i expand the years it shows the months but unfortunately the ones that previously had #value, are not included in the years/months, and are shown separately after the years.

Do you have any idea how i can solve this one as well?

Once again, thank you so very much for your help!

[#6]

this issue is caused that whenever value is returned by formula

=IFERROR(DATEVALUE(A3)+TIMEVALUE(A3),TEXT(A3,"General")) excel still detects that as text

so fix this
just put +0 at the end of the formula

like this =IFERROR(DATEVALUE(A3)+TIMEVALUE(A3),TEXT(A3,"General"))+0

[#7]

Unfortunately this is not working. I have added the "+0" to the I2 column and it returns "#Value". If i add the "+0" to the I3, I4, I5 it is working and again, if i add it to the I6 - not working again.

Any other thoughts on this?

[#8]

then you should try this formula

=IFERROR(DATEVALUE(A2)+TIMEVALUE(A2),TEXT(A2,"General")+0)

plz see it in attached file.

[#9]

The same result :( #Value

[#10]

is it possible to post a dummy sample workbook?

[#11]

Please see attached the dummy workbook.

[#12]

in my machine. it works.

plz see attached.

[#13]

That doesn't make sense :(

Anyway, even in your dummy workbook, the formatting is not the same - you have for the first two values showing only the date, and the next three values show the date and also the time.

How can that be solved?

[#14]

i fixed the formats, see attached plz