Changing date format of cells

[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.

 

[hansyt]

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

 

[hansyt]

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

 

[Md Sahir]

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.

hi

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