Month Function on UK dates formats (dd/mm/yyy)
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 |
Month Function on UK dates formats (dd/mm/yyy)
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 -- Gary''s Student - gsnu201001 "Gene Moirao" wrote: 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 |
Month Function on UK dates formats (dd/mm/yyy)
=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 "Gene Moirao" wrote in message ... 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 |
All times are GMT +1. The time now is 11:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com