Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with date format mm/dd/yyyy vs. dd/mm/yyyy
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with date format mm/dd/yyyy vs. dd/mm/yyyy
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. -- David Biddulph "Dream" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with date format mm/dd/yyyy vs. dd/mm/yyyy
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 "Dream" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with date format mm/dd/yyyy vs. dd/mm/yyyy
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 "Dream" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format date dd.mm.yyyy to dd/mm/yyyy | Excel Discussion (Misc queries) | |||
change date format from dd/mm/yyyy to mm/yyyy | Excel Discussion (Misc queries) | |||
format cells from mm/dd/yyyy to yyyy | New Users to Excel | |||
How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy | Excel Worksheet Functions | |||
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel |