One more....I promise

[rae30]

How can I make Excel understand that 01/02/04 is Feb 1st 2004, not Jan 2nd 2004.


Ray

 

[Aladin Akyurek]

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

 

[Guitarde]

I think you got to change the Regional settings in the control pannel.
That's what drives how the dates have to be entered in Excel.

 

[rae30]

Why wont 27/06/03 work I'm getting "#VALUE!"

Ray

 

[rae30]

ADVERTISEMENT

I don't want to change my system settings for one project.

I auto filled the formula down...it seems to work for only when the day is> 12.

 

[Aladin Akyurek]

I don't want to change my system settings for one project.

I auto filled the formula down...it seems to work for only when the day is> 12.

Because 27/06/03 is text (text if day > 12).

Try:

=IF(N(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)),DATE(2000+RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2)))

It's of course better to try to fix this problem at the source or during the import.

 

[rae30]

Your my new bestest friend.

Thanks a lot, you just saved me 2 hours of keying dates.

Ray