Hi.
Is there a way to convert a date formatted cell to a number?
I’ve played with Datevalue and if I add (“02 Jul 2022”) it returns 44744.
Can you use Datevalue but get the 02 Jul 2022 from a date formatted cell?
TIA
Andy C
44744 is the correct cell value. Just apply any number format you like. Since a spreasdheet is an arithmetic calculator rather than a daatabase, number and text are the only data types…Iit does not know “date” or “time” as true data types. 44744 formatted as date is interpreted as day number 44744 after day zero which was 1899-12-30.
yes… format the cell to … NumberformatCode: dd mmm yyyy
but anyway with a literal date you should use iso:
=DATEVALUE("2022-07-02")
or otherwise:
=DATE( 2022 ; 7 ; 2 )
Thanks for the two replies. I suspect I didn’t make myself clear.
I have a date formatted cell, say B2,. Then in another cell I’d like to use Datevalue but rather than entering the actual literal date, I’d like to use the date in B2.
Something along the lines of Datevalue(B2) or (“B2”) - they don’t work !
Is there a formula that would do that?
It’s not a big deal, just interested. Cheers.
Are you sure you made things clear enough to yourself?
DATEVALUE() expects a string (text type!) passed to it that can be recognized by LibreOffice as a date. The result is then returned to the cell as number type, and can be formatted based on any NumberFormat.
To get the same date as you have already in B2 in a second cell simply enter the formula =B2 there.
BTW: The only textual date format recognized under all locales by DATEVALUE() is ISO 8601 extended: YYYY-MM-DD
1 Like
The correct formula if B2 contains actually a Date-formatted Integer like 44542 is
=B2
If B2 contains a string wich human beings can read as text (like 2022-07-02 usualy copied from other places) you could use
=DATEVALUE(B2)
The main thing is to accept, your 44744 is the internal value for your date. So if you use a formula like 44744 +1 you get the next day and +7 advances one week.
If you use the buttons for date-formatting this doesn’t change the value (try currency if you like) but only the text shown to you.
The problem arises as Calc can also work with text and convert values. Therefore we need DATEVALUE and other functions sometimes, but they only work in the right circumstances.
Many thanks fro the explanation. 
Many thanks to all those who responded. The penny’s just dropped that all the data is just numbers and I can do what I require. Cheers