Date field from text field

[Jack Deuce]

I have 2 sheets containing two dates that I need to compare. One date
is in the serial date format, ie, shows as 07/21/04 (internal 39650).
The other date field has been formatted as text with a date containing
20090721. How can I convert this text date to a formatted date field
that can be used as the serial date for the date compare?

 

[Glenn]

I have 2 sheets containing two dates that I need to compare. One date
is in the serial date format, ie, shows as 07/21/04 (internal 39650).
The other date field has been formatted as text with a date containing
20090721. How can I convert this text date to a formatted date field
that can be used as the serial date for the date compare?

One way:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[Lars-Åke Aspelin]

I have 2 sheets containing two dates that I need to compare. One date
is in the serial date format, ie, shows as 07/21/04 (internal 39650).
The other date field has been formatted as text with a date containing
20090721. How can I convert this text date to a formatted date field
that can be used as the serial date for the date compare?

If the cell with 20090721 is cell H1, try the following formula:

=DATE(MID(H1,1,4),MID(H1,5,2),MID(H1,7,2))

Hope this helps / Lars-Åke

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD>Finish.


Gord Dibben MS Excel MVP