helpmeplz_
New Member
- Joined
- Aug 25, 2017
- Messages
- 6
Hi,
Thank you in advance for the help. I have been trying to calculate S8:Y9 for a couple of days now. It's turning into a mess of nested if statements and I'm still having trouble with all dates that I test. Essentially, given an Option of A or B in C8:C9, break out the Accepted Benefit in J8:J9 into the correct fiscal years in S8:Y9 according to the entered Implementation Date and End Date in N8:O9. In R1:Y5, you see the start dates and end dates of the fiscal years according to Option A or B.
My problem is that if I test many different implementation dates, sometimes the formulas break out the FY Benefit into the correct year and sometimes not. I am particularly having an issue with the formulas removing or adding 1 day to a fiscal year than they should, especially if you test implementation dates around the end date of Option A or B, or dates that span many fiscal years.
Thank you in advance for the help. I have been trying to calculate S8:Y9 for a couple of days now. It's turning into a mess of nested if statements and I'm still having trouble with all dates that I test. Essentially, given an Option of A or B in C8:C9, break out the Accepted Benefit in J8:J9 into the correct fiscal years in S8:Y9 according to the entered Implementation Date and End Date in N8:O9. In R1:Y5, you see the start dates and end dates of the fiscal years according to Option A or B.
My problem is that if I test many different implementation dates, sometimes the formulas break out the FY Benefit into the correct year and sometimes not. I am particularly having an issue with the formulas removing or adding 1 day to a fiscal year than they should, especially if you test implementation dates around the end date of Option A or B, or dates that span many fiscal years.
Please help with S8 to Y9.xlsx | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | AE | ||||||||
1 | Closest Date B: | 1/31/2021 | 1/31/2022 | 1/31/2023 | 1/31/2024 | 1/31/2025 | 1/31/2026 | 1/31/2027 | |||||||||||||||||||||||||
2 | Start Date B: | 2/2/2020 | 1/31/2021 | 1/30/2022 | 1/29/2023 | 2/4/2024 | 2/2/2025 | 2/1/2026 | |||||||||||||||||||||||||
3 | End Date B: | 1/30/2021 | 1/29/2022 | 1/28/2023 | 2/3/2024 | 2/1/2025 | 1/31/2026 | 1/30/2027 | |||||||||||||||||||||||||
4 | Start Date A: | 9/1/2019 | 9/1/2020 | 9/1/2021 | 9/1/2022 | 9/1/2023 | 9/1/2024 | 9/1/2025 | |||||||||||||||||||||||||
5 | End Date A: | 8/31/2020 | 8/31/2021 | 8/31/2022 | 8/31/2023 | 8/31/2024 | 8/31/2025 | 8/31/2026 | |||||||||||||||||||||||||
6 | Summary | $0 | $0 | $0 | $0 | $0 | $10,000,000 | $0 | $0 | $9,972,603 | $27,397 | $0 | $0 | $0 | $0 | $0 | |||||||||||||||||
7 | Option | Accepted Benefit | Implementation Date | End Date | FY20 Benefit (Accepted) | FY21 Benefit (Accepted) | FY22 Benefit (Accepted) | FY23 Benefit (Accepted) | FY24 Benefit (Accepted) | FY25 Benefit (Accepted) | FY26 Benefit (Accepted) | FY/CY Check | |||||||||||||||||||||
8 | A | $5,000,000 | 9/1/2019 | 8/31/2020 | $5,000,000 | $0 | $0 | $0 | $0 | $0 | $0 | ||||||||||||||||||||||
9 | B | $5,000,000 | 2/2/2020 | 2/1/2021 | $4,972,603 | $27,397 | $0 | $0 | $0 | $0 | $0 | ||||||||||||||||||||||
Track |
Cell Formulas | ||
---|---|---|
Range | Formula | |
T1:Y1 | T1 | =DATE(YEAR(S1)+1,MONTH(S1),DAY(S1)) |
U2 | U2 | =T3+1 |
S3:Y3 | S3 | =IF((TEXT(S1,"ddd")="Sat"),S1+7*(WEEKDAY(S1)>7),IF(ABS((CEILING(S1-1,7))-S1)>ABS((FLOOR(S1-1,7))-S1),FLOOR(S1-1,7),CEILING(S1-1,7))) |
M6,E6:K6 | E6 | =SUM(E8:E20) |
S6:Y6 | S6 | =SUM(S8:S21) |
S8:S9 | S8 | =IF($C8="B",IF(AND($N8<=S$3,OR($N8>=S$2,$O8>=S$2)),IF($N8=S$3,1,S$3-$N8)*($J8/365),0),IF(AND($N8<=S$5,OR($N8>=S$4,$O8>=S$4)),IF($N8=S$3,1,(S$5-$N8))*($J8/365),IF($N8=S$5,1,(S$5+1-$N8))*($J8/365))) |
T8:Y8 | T8 | =IF($C8="B",IF(IF(AND($N8<=T$3,OR($N8>=T$2,$O8>=T$2)),IF(S$3=$N8,$O8-1-T$2,$O8+1-T$2))>365,IF(AND($N8<=T$3,OR($N8>=T$2,$O8>=T$2)),(IF(T$3=$N8,T$3+1-$N8,T$3-$N8))*($J8/365),0),IF(AND($N8<=T$3,OR($N8>=T$2,$O8>=T$2)),IF(S$3=$N8,$O8-1-T$2,$O8-T$2)*($J8/365),0)),IF(IF(AND($N8<=T$5,OR($N8>=T$4,$O8>=T$4)),IF(S$5=$N8,($O8-1-T$4),$O8+1-T$4))>365,IF(AND($N8<=T$5,OR($N8>=T$4,$O8>=T$4)),IF(T$4=$N8,(T$5-$N8),(T$5+1-$N8))*($J8/365),0),IF(AND($N8<=T$5,OR($N8>=T$4,$O8>=T$4)),IF(S$5=$N8,$O8-T$4,($O8+1-T$4))*($J8/365),0))) |
T9:Y9 | T9 | =IF($C9="B",IF(IF(AND($N9<=T$3,OR($N9>=T$2,$O9>=T$2)),IF(S$3=$N9,$O9-1-T$2,$O9+1-T$2))>365,IF(AND($N9<=T$3,OR($N9>=T$2,$O9>=T$2)),(IF(T$3=$N9,T$3+1-$N9,T$3-$N9))*($J9/365),0),IF(AND($N9<=T$3,OR($N9>=T$2,$O9>=T$2)),IF(S$3=$N9,$O9-1-T$2,$O9+1-T$2)*($J9/365),0)),IF(IF(AND($N9<=T$5,OR($N9>=T$4,$O9>=T$4)),IF(S$5=$N9,($O9-1-T$4),$O9+1-T$4))>365,IF(AND($N9<=T$5,OR($N9>=T$4,$O9>=T$4)),IF(T$4=$N9,(T$5-$N9),(T$5+1-$N9))*($J9/365),0),IF(AND($N9<=T$5,OR($N9>=T$4,$O9>=T$4)),IF(S$5=$N9,$O9-T$4,($O9+1-T$4))*($J9/365),0))) |
O8:O9 | O8 | =N8+365 |
AE8 | AE8 | =IF(OR((SUM(S8:Y8)-SUM(Z8:AD8)>0),(SUM(Z8:AD8)-SUM(Z8:AD8)>0)),"Check","") |
AE9 | AE9 | =IF(OR((SUM(S9:Y9)-SUM(Z9:AD9)>0),(SUM(Z9:AD9)-SUM(S9:Y9)>0)),"Check","") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AE8:AE9 | Cell Value | contains "Check" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D8:D9 | List | Capex,Opex,Both |
P8:P9 | List | =#REF! |