Changing date format of cells

  • Thread starter Thread starter chaitanyag
  • Start date Start date
C

chaitanyag

Hi,
I have an excel sheet that contains some dates (sample given below),
entered in the "dd/mm/yyyy" format. But it seems excel is interpreting
them as "mm/dd/yyyy". Whenever i try to do any date related stuff, such
as change the format or group them together etc. It throws up on dates
like "15/1/2006" and interprets "11/1/2006" as November - 1st - 2006

Is there any way to change the way excel interprets these dates? Make
them read them as "dd/mm/yyyy" rather than "mm/dd/yyyy"? Failing that,
is there someway to automatically convert (using a macro for example)
from one date format to other by swapping the day month fields? I just
don't want to sit there and change/re-enter the dates manually

9/1/2006
10/1/2006
11/1/2006
15/1/2006
16/1/2006
16/1/2006

Any help is much appreciated.
 
The general format of dates on your system is defined in Windows,
system panel, regional and language settings. If you enter a date it is
interpreted this way. If your settings were: DD.MM.YYYY and you would
enter 02.25.2006 it would be treated as text, not date.

Once you have antered a valid date, the formatting (format, cells, date
or custom) defines how you want the date displayed.

Use these to find how your entered dates are interpreted:

=day(a1)
=month(a1)
=year(a1)

Try formatting an input field and choose the custom formatting option
and in the "type" box enter: DD/MM/YYYY (use capital letters)

If that doesn't help, have a look how your fields are formatted (text
or date) and you may have to come back.

Hans
 
The general format of dates on your system is defined in Windows,
system panel, regional and language settings. If you enter a date it is
interpreted this way. If your settings were: DD.MM.YYYY and you would
enter 02.25.2006 it would be treated as text, not date.

Once you have antered a valid date, the formatting (format, cells, date
or custom) defines how you want the date displayed.

Use these to find how your entered dates are interpreted:

=day(a1)
=month(a1)
=year(a1)

Try formatting an input field and choose the custom formatting option
and in the "type" box enter: DD/MM/YYYY (use capital letters)

If that doesn't help, have a look how your fields are formatted (text
or date) and you may have to come back.

Hans
 
[email protected] said:
Hi,
I have an excel sheet that contains some dates (sample given below),
entered in the "dd/mm/yyyy" format. But it seems excel is interpreting
them as "mm/dd/yyyy". Whenever i try to do any date related stuff, such
as change the format or group them together etc. It throws up on dates
like "15/1/2006" and interprets "11/1/2006" as November - 1st - 2006

Is there any way to change the way excel interprets these dates? Make
them read them as "dd/mm/yyyy" rather than "mm/dd/yyyy"? Failing that,
is there someway to automatically convert (using a macro for example)
from one date format to other by swapping the day month fields? I just
don't want to sit there and change/re-enter the dates manually

9/1/2006
10/1/2006
11/1/2006
15/1/2006
16/1/2006
16/1/2006

Any help is much appreciated.
Click to expand...





hi

change the format in regional and language options on the control panel
 
Back
Top