Help with date format mm/dd/yyyy vs. dd/mm/yyyy

[Dream]

Hi all,

I appreciate your help on the following:

I have a list of dates that i recieved as excel file. the dates are
formatted as month/day/year but the truth of the matter is that it is
day/month/year...for example,

one data point is shown as 12/8/2009, when i click to see its format it is
Date format *3/14/2001 so excel thinks it is december 8 2009...now i want
excel to know that the date is 12/8/2009 which is 12 august 2009...i tried
text to column but the problem is that excel still thinks that the first
number is the month but i want it to know that it is the day not the
month....if i try to change the format to dd/mm/yyyy from custom date, still
excel is confused so it switched both figures but it is wrong to start with!!!
i tried to copy past the value and try to text to column with / being the
separator, but then excel will give me the serial number of the date!!

i very much appreciate your help and thanks in advance

 

[David Biddulph]

Formatting the cell will change only how the date is displayed, but not what
the value is of the date stored.

The decision on interpretation of ambiguous dates such as 12/8/2009 when you
input them is governed not by the cell formatting in Excel but by Windows
regional Options (in Control Panel). Use this to get the interpretation
right before you put your data into Excel.

 

[Bernie Deitrick]

Dream,

Excel will interpret dates where the day is 12 or less as being mm/dd rather than dd/mm, and cases
where the day would result in an incorrent month as dd/mm.

So, you could try this: Use a column of formulas to convert the dates... for dates starting in cell
A2, use

=IF(DAY(A2)<13,DATE(YEAR(A2),DAY(A2),MONTH(A2)),A2)

and copy down to match your column of dates, then copy and paste values over your original dates.

HTH,
Bernie
MS Excel MVP

 

[YESHWANT]

Hi Dream,

It's a basic problem of your regional settings. kindly set the date
properly under the same as "dd/mm/yyyy" and your problem is over.

To do the same, click on start, control panel, regional settings/regional &
language options, click on customize, select date pane and enter "short date
format " as "dd/mm/yyyy.

click yes below, if it helps