How to Format pre 1900's dates to have leading zero on dates 1-9

[seansr]

I have a excel spreadsheet with 34,000 rows

I have 1 column that has a date in the format dd mmm yyyy which has given me what I want except the pre 1900 dates


if a date is 01 Jan 1921 then you can see it is 01

if a date is 1 Jan 1820 then you can see it is 1 not 01


what I want to do is standardise it so they al show 01 not 1 just so that it make the column much more pleasing to the eye and profisional looking This data will be produced as a CD for sale


I can change the format to d mmmm yyyy and it take out all the leading zeros on the post 1900 but it make column untidy. Data will be sorted in Alpabetical order on Surname

 

[NARAYANK991]

Hi Sean ,


You can use the LEN function to find out the length of the date string ; since these are pre-1900 dates , they are not really numeric values ( which post-1900 dates are ) , and therefore they are text strings.


If you find that the length of the date string is less than 11 , then you can prefix it with a 0 , through :


=IF(LEN(date_string)<11,"0"&date_string,date_string)


Narayan

 

[seansr]

Thanks that worked a treat