Datevalue command is not working as i want start of month from 1st jan in the cell as,it is working fine in microsoft office like =Datevalue("1"&jan) for 1st date of jan.
When you look at your Excel spreadsheet next year, what will the number be then? 44562
or will it have changed to 44927 just by the passing of time?
iits just showing erroe 502 …not showing any number
You were asked for the behavior of your Excel.
That’s what should be expected as I am explaining in my answer below. Neither “1jan” Nor “1”&jan will pass something acceptable as a date. Only if jan is a name returning the string value " jan" or similar (Including the space!) Calc may accept it as a date.
What do you get in the same cell of your sheet if you enter =jan there and nothing else?.
To apply DATEVALUE() without mentioning a year, without a space between tha day’s number and the month, and using fancy and gravely locale-dependent formats is a very bad idea, but MS may do so. They are well known for bad ideas. Be aware, however, of the fact that the formula will return a different date when recalculated on opening the file next year. This remains true even if you display that date in a format not regarding the year.
If you use DATEVALUE("1 " & P19) you would even get in Calc what you expect in a locale knowing the abbreviation “jan” for the first month of a year. (Regard the year-issue, however.). In what way Excel guesses or has assigned the string "jan" to the name jan I don’t know.
===editing (forced by nonsense action of Discourse)===
You may study the attached example to better understand the issue.
Alas! I can’t assure the example to work as intended in you locale or with your additional settings probably afflicting number formats. Locales are a mess.
Always design your sheets for a locale-independent working wherever possible. To avoid the usage of DATEVALUE() may be a part of that concept. It’s one of the “evil functions”.
datevalueIssues.ods (15.6 KB)
Concerning my remark on Discourse:
“No more than 3 consecutive replies are allowed. Please edit your previous reply, or wait for someone to reply to you.”
(@administrators: Might someone please tell the Discourse to respect the contributors. If such a restriction should be acceptable at all, it must show when the intended post is opened in an empty frame)
2 Likes
NOT WORKING …
example also show error 502 when i refresh cell
What’s “not working”? Are you talking of my example attached above?
It surely works for me as described. To avoid complications in global communication I’m using English(UK) for the UI and for my “locale” as well (despite being a German in Germany).The default language of my sheets is ‘Default - English (UK)’ then.
Did I already mention that locales are a mess?
Use locale-independent formulas! My example is containing some.
There are also working examples for DATEVALUE(), but I wouldn’t recommend to use that function anywhere except if the date-string is in ISO 8601 extended format.