Month Function on UK dates formats (dd/mm/yyy)

[Gene Moirao]

Hi, (Excel 2007)
I'm in the US, created 2 tabs, 1 with US dates (formatted as DATE) and the
other tab with UK dates (also formatted as DATE).

File sent to UK to enter dates (dd/mm/yyyy)

=MONTH works find pulling out months from US dates, but fails on some UK
dates.
FAILS on =month(13/1/2010) #VALUE! but ok with =month(12/1/2010). I suspect
it is failing because it's "out of date range".

My goal is to pull out the month from all UK dates.

THANK for your help!
Gene

 

[Gary''s Student]

It may be for than just a formatting issue. For example in A1 enter:
1/13/2010 (US format)
In A2 enter:
=A1 and format as dd/m/yyyy
You will see:

1/13/2010
13/1/2010

However, both MONTH(A1) and MONTH(A2) will equal 1

 

[Fred Smith]

=Month(..) will return the correct month regardless of how the date is
formatted. I believe you have a data entry issue.

How Excel interprets the entry of dates is controlled by the computer's
Windows Regional settings, not by the cell's format. You need to check these
settings on both computers. Code and formulas are independent of the cell's
format. This way, you don't have to program two versions of anything. One
version will suffice.

Regards,
Fred