date as text

Testing DATETOTEXT with today's date (June 16, 2011) and the result cell set to "General" (which is a Number format, not Text), in AppleWorks, I get "16/6/2011", not the serial number for the date.

Using =A4, with the cell format set to "General", I get the number 39248.

Which is the number of days since January 1, 1904.

If that's the result you're looking for, DATEDIF will give it to you in Numbers.

In Numbers, put Jan 1, 1904 in B2, Jun 16, 2011 in C2 and (for a numerical result) the formula below in D2:

=DATEDIF(B2,C2,"D")

For the same result in Text, use this variation:

=""&DATEDIF(B2,C2,"D")

Regards,

Barry

May you read carefully ?

The formula calculate the difference between two dates.

You used DATEVALUE to convert a string matching a given system setting into a date

I use DATE which convert as date a list of three parameters.

It's DATEDIF which calculate the wanted serial number !

Of course, if you can't read the descriptions of the functions, I can't help you !

Yvan KOENIG (VALLAURIS, France) vendredi 17 juin 2011 11:20:53

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Date and Time value in B2, B2 formatted to show only date (in your choice of date format).

Formula in C2:

=""&B2

& is the concatenation operator, and joins a null string ( "" ) with the date value, taken as the displated text, in B2.

Regards,

Barry

Cliff Peters wrote:

="123"&DATEDIF(DATEVALUE("01-01-1904"),C2,"D")

You are using a localization dependent formula.

Better use one which isn't :

="123"&DATEDIF(DATE(1904,1,1),C2,"D")

Yvan KOENIG (VALLAURIS, France) jeudi 16 juin 2011 14:38:47

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

It appears that you didn't read carefully.

You used the function DATEVALUE but I use the function DATE.

My formula build on the fly a date according to your settings.

Date (1904,1,1) create 01/01/1904 on a machine using mm/dd/yyyy or dd/mm/yyyy format and 1904-01-01 on system using the international ISO format yyyy-mm-dd.

Yvan KOENIG (VALLAURIS, France) vendredi 17 juin 2011 10:33:12

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Thanks for the feedback, Cliff, and the explanation of what use you are making of the result.

Regards,

Barry