Forum Discussion
Formulas with apostrophe
Hi,
I am doing an excel training course and literally copying the formula word for word. Any formula that involves apostrophe comes back as an error. Why is this when it matches what is in my excel course?
Example formula below:
=DATEVALUE(''12-3-1987'')
Thanks all
Jo
4 Replies
JChapman-Cohen wrote: =DATEVALUE(''12-3-1987'')
But it is even better to use DATE(1987, 12, 3) or DATE(1987, 3, 12), whichever you mean.
And that's the point: 12-3-1987 is ambiguous. Is that Dec 3 or Mar 12?
Moreover, if you "share" (e.g. email) the workbook with someone that uses a different date form, the DATEVALUE formula will either return an error (e.g. if the date is 12-29-1987) or, worse, it will return an unintended date.
- Thank you, that's really helpful, i didn't appreciate how the formulas may not work correctly wither when sharing a workbook.
You are supposed to use double quotes " instead of two single quotes ' (apostrophes) next to each other.
=DATEVALUE("12-3-1987")
HansVogelaar Ah ha!!!! thank you! What a simple mistake but that answered has fixed so many formula issues i was having! thank you so much.
