Associating a date with a specific FY

christal

New Member
Joined
Apr 7, 2002
Messages
45
For exampe: I have a column of dates in column L, and I have dollar figures in columns C through J. The column headings in C - J are Fiscal Year 2002, Fiscal Year 2003, etc.... If the date range for the following FY's are as follows:

FY02: 10/01/01 - 09/30/02
FY03: 10/01/02 - 09/30/03
FY04: 10/01/03 - 09/30/04
FY05: 10/01/04 - 09/30/05
FY06: 10/01/05 - 09/30/06
FY07: 10/01/06 - 09/30/07

then the $ amount should appear in the appropriate FY and in the out years.

So if: a date should fall on 10/30/04, than the funds should appear in FY05, FY06, FY07. All other funds should not be displayed.

This one was real tough... I would greatly appreciate any help!

Thanks!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
OK, move your dollar amounts to one column, say column M.

Now, if your column headings in C-J are exactly as stated in your post and your dates are in column L and the money is in column M then in C2 paste:

=IF($L2<DATE(RIGHT(C$1,4),9,30),$M2,0)

and copy through column J and down.

Should work.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top