Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Fiscal Period based on Date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Fiscal Period based on Date
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Jake" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating Fiscal Period based on Date
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 + |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign period to date based on rules. | Excel Worksheet Functions | |||
Calculate a date based on period and year | Excel Worksheet Functions | |||
Lookup dates, fiscal period table | Excel Worksheet Functions | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Function: Two Month Date Spans From Fiscal Period - an example | Excel Programming |