Date Formats

B

Bob

I have about 4000 dates in a spreadsheet, some of the dates are in the
'mm/dd/yyyy' format and have no problem with them; however, interspersed
throughout the file are 'yyyy' dates only that tend to default to an
erroneous 'mm/dd/yyy' format. i.e., 1825 may get translated as 01/20/1905.
How can I have the 'mm/dd/yyyy' and 'yyyy' dates in the same column?
I've tried text and custom but still have no relief --

SgtBob
 
G

Gary''s Student

Say the dates are in column A, In B1 enter:

=IF(A1<3000,DATE(A1,1,1),A1) and copy down. The format column B as the date
format of your choice.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to add day to PWA 2016 date format? 0
Date Formats 4
is it possible to sort by approximate date? 2
Forcing a date entry format of dd/mm/yyyy 1
date 1
Convert dates stored as text 2
Date Maniplation 1
Excel date formate doesn't change 0

Top