Dim sDate As Date
Dim mYear As Long

sDate = ActiveSheet.Range("B2").Value
'for testing
'sDate = DateSerial(2008, 11, 1)

mYear = Year(sDate) - CBool((Month(sDate) 10))

MsgBox mYear

(In VBA, true is equal to -1)



jenn k wrote:

Hi all! I have run across an issue with data retrieval. At the company I
work for, the year runs from November 1, 2008-October 31, 2009. In one of
our forms, the user will enter a date such as 11/1/2008 in cell B2. Our path
for data retrieval is m:\2009\SALES TEAM\Midwest Territory.xls. For two
months (nov and dec) the year will be 2008. How can I change the mYear
variable below to accomodate the year 2008 when it actually resides in the
2009 folder? My apologies, I'm very new to excel and VBA. Thanks.

sdate = Range("B2").Value

mYear = Format(sdate, "yyyy")
mPath = "='m:\" & mYear & "\SALES TEAM\Midwest Territory.xls"
--
jenn k

--

Dave Peterson