Need Fiscal Year/ Calendar Year matching days formula!! Please help!!!

colinjmang

New Member
Joined
Oct 7, 2016
Messages
1
I work in grant budgeting and need to calculate fiscal year budgeted amounts based off of calendar year budgets. Usually the amount of calendar year days hits multiple fiscal years. See below;
Calendar YearTotal DaysFiscal YearTotal Days
Days in CY3653500715Days in FY0
Spend CategoryYear 1Year 2Year 3Spend CategoryFY14FY15FY16FY17
4/15/20154/14/20164/15/20163/31/20173/31/20173/31/2017Totals7/1/20136/30/20147/1/20146/30/20157/1/20156/30/20167/1/20166/30/2017Totals

<tbody>
</tbody>


I have DATEDIF to calculate total calendar year days so I can divide amounts by that to give daily rates. I have days in FY split in 2 columns since calendar year days can be (and usually are) across 2 different fiscal years. I need help calculating how many calendar year days hit in given fiscal year. ie how many "Year 1" days are in FY15 and another column for "Year 2" days hit in FY15. I need both because I will have to divide amounts on calendar years by the # of days this accounts for, then multiply by how many of those days are in said fiscal year.

Thank you so much for the help.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,144,158
Messages
5,722,824
Members
422,460
Latest member
VBA_Noob01

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
Top