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;
<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.
| Calendar Year | Total Days | Fiscal Year | Total Days | |||||||||||||||||
| Days in CY | 365 | 350 | 0 | 715 | Days in FY | 0 | ||||||||||||||
| Spend Category | Year 1 | Year 2 | Year 3 | Spend Category | FY14 | FY15 | FY16 | FY17 | ||||||||||||
| 4/15/2015 | 4/14/2016 | 4/15/2016 | 3/31/2017 | 3/31/2017 | 3/31/2017 | Totals | 7/1/2013 | 6/30/2014 | 7/1/2014 | 6/30/2015 | 7/1/2015 | 6/30/2016 | 7/1/2016 | 6/30/2017 | Totals |
<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.
