Converting numerical date to text

  • Thread starter Thread starter Debbie Alexander
  • Start date Start date
D

Debbie Alexander

Does anyone know how to convert to text and keep the
integrity of the numerical date? (example: 4/06/97 to
04061997)
I can make it look okay, however; the cell is still
reflecting the previous value.
 
Not sure what you mean by "the cell is still reflecting the previous
value".

1) Formatting doesn't affect the underlying value stored in the
cell. Since XL stores dates as integer offsets from a base date,
4/06/97 is the displayed value for the number 35526 (windows default
1900 date system). You can change the formatting to

Format/Cells/Number/Custom mmddyyyy

but that doesn't change the value (35526).

2) You can use the Text function to convert a value to Text:

A1: 35526 ==> can display as 4/6/1997 or 04061997, etc.
A2: =TEXT(A1,"mmddyyyy") ==> value is 04061997

the value in A2 is actually an eight-character text string.
 
Assuming the date is in cell "A4"
RIGHT("00" & MONTH(A4),2) & RIGHT("00" & DAY(A4),2) & YEAR(A4)

Then format the cell as text.

Did I come close to what you wanted? (Gettin' older and slower)

--
Regards;
Rob

Please reply to the NG.
I'm already up to my eyeballs in
Nigerian/South African get rich
letters as well as "Microsoft Critical Updates" et al.
 
Back
Top