Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem in VBA
Hi All
I'm importing data from an Access query in to an Excel pivot table and as I many of these imports to do I set the following constants in a standatd module. Private Const YearStart As Date = #1/4/2007# Private Const YearEnd As Date = #31/3/2008# The Dates I need are 1st April 2007 to 31st March 2008 however the date #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for the 1st january 2007. I've chacked the Windows XP Regional setting for the date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any suggesstions as to why the date #1/4/2007# is being changed? TIA johnb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem in VBA
VBA is pretty USA centric.
I'd try the USA settings: #4/1/2007# (for April 1, 2007) Personally, I'd try to remove any ambiguity: Private YearStart as Date .... Later yearstart = dateserial(2007,4,1) === I have no idea how Access queries work, though. johnb wrote: Hi All I'm importing data from an Access query in to an Excel pivot table and as I many of these imports to do I set the following constants in a standatd module. Private Const YearStart As Date = #1/4/2007# Private Const YearEnd As Date = #31/3/2008# The Dates I need are 1st April 2007 to 31st March 2008 however the date #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for the 1st january 2007. I've chacked the Windows XP Regional setting for the date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any suggesstions as to why the date #1/4/2007# is being changed? TIA johnb -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem in VBA
Hi Dave
Yeah. good point to define it later I shall try it! regards johnb "Dave Peterson" wrote: VBA is pretty USA centric. I'd try the USA settings: #4/1/2007# (for April 1, 2007) Personally, I'd try to remove any ambiguity: Private YearStart as Date .... Later yearstart = dateserial(2007,4,1) === I have no idea how Access queries work, though. johnb wrote: Hi All I'm importing data from an Access query in to an Excel pivot table and as I many of these imports to do I set the following constants in a standatd module. Private Const YearStart As Date = #1/4/2007# Private Const YearEnd As Date = #31/3/2008# The Dates I need are 1st April 2007 to 31st March 2008 however the date #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for the 1st january 2007. I've chacked the Windows XP Regional setting for the date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any suggesstions as to why the date #1/4/2007# is being changed? TIA johnb -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problem in VBA
I'm using a UK date format eg dd/mm/yyyy and I use a Yearstart as a Const coz
it's referred to many time in the module. So I need to force a UK date format and my brains on strike. Ant ideas? regards johnb "johnb" wrote: Hi Dave Yeah. good point to define it later I shall try it! regards johnb "Dave Peterson" wrote: VBA is pretty USA centric. I'd try the USA settings: #4/1/2007# (for April 1, 2007) Personally, I'd try to remove any ambiguity: Private YearStart as Date .... Later yearstart = dateserial(2007,4,1) === I have no idea how Access queries work, though. johnb wrote: Hi All I'm importing data from an Access query in to an Excel pivot table and as I many of these imports to do I set the following constants in a standatd module. Private Const YearStart As Date = #1/4/2007# Private Const YearEnd As Date = #31/3/2008# The Dates I need are 1st April 2007 to 31st March 2008 however the date #1/4/2007# is changed by Excel to #4/1/2007# so that the pivottable show for the 1st january 2007. I've chacked the Windows XP Regional setting for the date and are dd/mm/yyyy which is ok. Is there another setting somewhere? Any suggesstions as to why the date #1/4/2007# is being changed? TIA johnb -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
Us date versus EU date problem | Excel Programming | |||
DAte problem again | Excel Programming | |||
Date problem using VBA | New Users to Excel |