Calculating Fiscal Period based on Date

[Jake]

Hello All,
I have an project i'm working on to determine fiscal periods based on
date of invoice. I have one workbook ("FiscalPeriods.xls") that has the
date ranges for each fiscal period for the past three years. ie;

Period PeriodStartDate PeriodEndDate
1 1/1/2007 1/27/2007
2 1/28/2007 2/24/2007
3 2/25/2007 3/31/2007
4 4/1/2007 4/28/2007


The other workbook has about 30,000 records of invoices over the past 3
years that have a specific "InvoiceDate". What I want to do is take the
first record, analyze the date, and return a new column called "Period"
and the subsequent period number.

I just need any ideas on which direction I should go in. Any
suggestions are much appreciated.

Thanks,
JB

 

[Guest]

In the invoice workbook, insert a new column
and assuming that the invoice date is in column 1 enter the following
formula adjusting also for the list of dates:

=SUMPRODUCT(([FiscalPeriods.xls]Sheet1!$B$2:$B$5<=A1)*(A1<=[FiscalPeriods.xls]Sheet1!$C$2:$C$5),[FiscalPeriods.xls]Sheet1!$A$2:$A$5)

Then copy it down, calculate and then copy and paste special values the
column.

Note in FiscalPeriods.xls sheet1 column a is the period number, column b is
the start date and column c is the end date.

You also can check for bad dates for any dates with 0.

 

[Jake]

Is this something that can be done in VBA? The reason I ask, every few
days I have to import a file with updated records for the past few
days. so each time I import it the existing file is overwritten. I have
a VBA Macro that currently makes modifications to this invoice file and
would like to have it call a new macro to perform the period function.
Is that possible?

Thanks,
Jake
+