date format convert

[3Suk]

a file is provided by another company. The date format is dd/mm/yyyy.
For example :
in A1 "9/3/2012" (suppose is March 9, 2012)
in B1 "22/12/2012" (suppose is December 22, 2012)

My problem is when I use formula "=month(A1)", it returns September while "=month(B1)", it returns #VALUE!.

But there is no such date format can be defined in Excel, pls advise how can I change the format.

Thanks in advance.
Patrick.

 

[3Suk]

a file is provided by another company. The date format is dd/mm/yyyy.

For example :

in A1 "9/3/2012" (suppose is March 9, 2012)

in B1 "22/12/2012" (suppose is December 22, 2012)



My problem is when I use formula "=month(A1)", it returns September while "=month(B1)", it returns #VALUE!.



But there is no such date format can be defined in Excel, pls advise how can I change the format.



Thanks in advance.

Patrick.

I made it after I changed the format in control panel, using UK format.
anything can do in order to avoid using control panel format setting? say using setting in Excel or formula?

Thanks,
PL.

 

[Ron Rosenfeld]

I made it after I changed the format in control panel, using UK format.
anything can do in order to avoid using control panel format setting? say using setting in Excel or formula?

If the file that is provided by the other company is a TEXT file (or CSV file), then you can IMPORT it into Excel (using the Get External Data tab on the Data Ribbon) and the text import wizard should open. That will give you the opportunity to specify the date format.

 

[3Suk]

If the file that is provided by the other company is a TEXT file (or CSV file), then you can IMPORT it into Excel (using the Get External Data tab on the Data Ribbon) and the text import wizard should open. That will give you the opportunity to specify the date format.

Ron,
Thanks for your advice.
Patrick.