Apr 18 2018
04:07 AM
- last edited on
Jul 25 2018
11:43 AM
by
TechCommunityAP
Apr 18 2018
04:07 AM
- last edited on
Jul 25 2018
11:43 AM
by
TechCommunityAP
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!
Apr 18 2018 05:29 AM - edited Apr 18 2018 05:29 AM
can you try with this
=IFERROR(DATEVALUE(B2)+TIMEVALUE(B2),TEXT(B2,"General"))
Apr 18 2018 07:19 AM
Not working :(
Apr 18 2018 07:39 AM
Solutionyou 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.
Apr 19 2018 12:01 AM
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!
Apr 24 2018 08:53 AM - edited Apr 24 2018 08:54 AM
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
Apr 27 2018 03:11 AM
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?
Apr 27 2018 03:18 AM
then you should try this formula
=IFERROR(DATEVALUE(A2)+TIMEVALUE(A2),TEXT(A2,"General")+0)
plz see it in attached file.
Apr 27 2018 04:26 AM
The same result :( #Value
Apr 27 2018 07:17 AM
May 02 2018 03:38 AM
Please see attached the dummy workbook.
May 22 2018 06:47 AM
in my machine. it works.
plz see attached.
May 22 2018 06:50 AM
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?
May 22 2018 07:13 AM
i fixed the formats, see attached plz
Apr 18 2018 07:39 AM
Solutionyou 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.