Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup function which recognises many dates in one month | Excel Discussion (Misc queries) | |||
function to fill all days of month to end of month | Excel Worksheet Functions | |||
function to fill all days of month to end of month | Excel Worksheet Functions | |||
Dates - Several Days In a month to month only | Excel Discussion (Misc queries) | |||
When using MONTH function on Blank Cell!! Returns Month=Jan! | Excel Discussion (Misc queries) |