Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DSCAVOTTO
 
Posts: n/a
Default Lookup dates, fiscal period table

I am trying to create a lookup table to determine the fiscal period of a
transaction. For example,a transaction dated Feb. 21, 2006 would be in period
14 in a table that started with Jan 2005. The file I am working with is
fairly substantial with numerous transactions every month. I will need to
have at least a three year table.

Period Month
1 Jan-05
2 Feb-05
3 Mar-05
13 Jan-06
14 Feb-06

Thanks for your help.
--
Dave
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Lookup dates, fiscal period table

If you've got the analysis tool pak (tools addins, ...), you could use
datedif rather than a table. For example, if the transaction date is in A1,
then the period would be =datedif(date(2004,12,1),a1,"m"). That will
calculate the number of complete months since 12/1/04 so that 1/1/05 starts
period number 1.
--Bruce

"DSCAVOTTO" wrote:

I am trying to create a lookup table to determine the fiscal period of a
transaction. For example,a transaction dated Feb. 21, 2006 would be in period
14 in a table that started with Jan 2005. The file I am working with is
fairly substantial with numerous transactions every month. I will need to
have at least a three year table.

Period Month
1 Jan-05
2 Feb-05
3 Mar-05
13 Jan-06
14 Feb-06

Thanks for your help.
--
Dave

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DSCAVOTTO
 
Posts: n/a
Default Lookup dates, fiscal period table

Thanks - that works great.
--
Dave


"DSCAVOTTO" wrote:

I am trying to create a lookup table to determine the fiscal period of a
transaction. For example,a transaction dated Feb. 21, 2006 would be in period
14 in a table that started with Jan 2005. The file I am working with is
fairly substantial with numerous transactions every month. I will need to
have at least a three year table.

Period Month
1 Jan-05
2 Feb-05
3 Mar-05
13 Jan-06
14 Feb-06

Thanks for your help.
--
Dave

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Need Help with lookup values in Table cank Excel Discussion (Misc queries) 4 November 10th 05 06:40 PM
How do I lookup a corresponding value in another table Des Excel Worksheet Functions 2 November 7th 05 12:28 PM
Multiple table lookup KG Excel Discussion (Misc queries) 1 June 3rd 05 05:39 AM
Index table lookup anomaly Carole O Excel Worksheet Functions 9 December 9th 04 05:33 PM


All times are GMT +1. The time now is 05:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"