MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 348
- Office Version
- 2013
- Platform
- Windows
I am having great difficulty with a leap year with respect to the formulas you see in the attached Xl2bb examples.
In the sheet ForecastHistory2023 you will see all the formulas are working correct, but in the sheet ForecastHistory2024 the initial formulas in C5 & C6 and probably consequently throughout the remainder of column “C” where these 2 date formulas exist are not working.
What am I missing?
I have tried several different variations and nothing seems to work.
Any help will be much appreciated.
The first Xl2bb is from sheet ForecastHistory2023
And
The second Xl2bb is from sheet ForecastHistory2024
In the sheet ForecastHistory2023 you will see all the formulas are working correct, but in the sheet ForecastHistory2024 the initial formulas in C5 & C6 and probably consequently throughout the remainder of column “C” where these 2 date formulas exist are not working.
What am I missing?
I have tried several different variations and nothing seems to work.
Any help will be much appreciated.
The first Xl2bb is from sheet ForecastHistory2023
And
The second Xl2bb is from sheet ForecastHistory2024
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:C2,E2 | C2 | =Forecast!E2 |
| H2 | H2 | =IF(A3=$A$1,H1,H1+1) |
| B3 | B3 | =J1 |
| E3,E7 | E3 | =B5 |
| G3,G7 | G3 | =B6 |
| H3,H7 | H3 | =B4 |
| B4,B8 | B4 | =ISOWEEKNUM(C3) |
| C4,C8 | C4 | =C6 |
| H4,H8 | H4 | =IF(A3=$A$1,H3,H3) |
| B5,B9 | B5 | =MAX(DATE(B3,1,1),DATE(B3,1,1)-WEEKDAY(DATE(B3,1,1),2)+(B4)*7+1) |
| H5,H9 | H5 | =IF(A3=$A$1,H4,H4) |
| B6,B10 | B6 | =MIN(DATE(B3+1,1,0),DATE(B3,1,1)-WEEKDAY(DATE(B3,1,1),2)+(B4+1)*7) |
| C6,C10 | C6 | ="Week "&B4&" of "&B3&" "&E6&" To "&G6&".png" |
| B7 | B7 | =J1 |
| C7 | C7 | =C3+7 |
| G6,G10 | G6 | =TEXT($B6,"mmmm dd") |
| H6,H10 | H6 | =IF(A3=$A$1,H5,H5) |
| E6,E10 | E6 | =TEXT($B5,"mmmm dd") |
| K4:K10 | K4 | =INDEX($C$3:$C$210,(ROW()-4)*4+4) |
| AnnualForecastHistory.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | 0 | 2024 | =MAX(DATE(B3,1,1),DATE(B3,1,1)-WEEKDAY(DATE(B3,1,1),2)+(B4)*7+1) | ||||||||||||
| 2 | Week # ð | 47 | Monday, November 22, 2021 | Sunday, November 28, 2021 | 1 | =MIN(DATE(B3+1,1,0),DATE(B3,1,1)-WEEKDAY(DATE(B3,1,1),2)+(B4+1)*7) | |||||||||
| 3 | Year | 2024 | Mon-01 January 2024 | Week of | January 08 | to | January 14 | 1 | |||||||
| 4 | Week Number | 1 | Week 1 of 2024 January 08 To January 14.png | 1 | 1 | Week 1 of 2024 January 08 To January 14.png | |||||||||
| 5 | Start Date | January 08 | 1 | 2 | Week 2 of 2024 January 09 To January 21.png | ||||||||||
| 6 | End Date | January 14 | Week 1 of 2024 January 08 To January 14.png | January 08 | January 14 | 1 | 3 | Week 3 of 2024 January 22 To January 28.png | |||||||
| 7 | Year | 2024 | Mon-08 January 2024 | Week of | January 09 | to | January 21 | 2 | 4 | Week 4 of 2024 January 29 To February 04.png | |||||
| 8 | Week Number | 2 | Week 2 of 2024 January 09 To January 21.png | 2 | 5 | Week 5 of 2024 February 05 To February 11.png | |||||||||
| 9 | Start Date | January 09 | 2 | 6 | Week 6 of 2024 February 12 To February 18.png | ||||||||||
| 10 | End Date | January 21 | Week 2 of 2024 January 09 To January 21.png | January 09 | January 21 | 2 | 7 | Week 7 of 2024 February 19 To February 25.png | |||||||
| 11 | Year | 2024 | Mon-15 January 2024 | Week of | January 22 | to | January 28 | 3 | 8 | Week 8 of 2024 February 26 To March 03.png | |||||
| 12 | Week Number | 3 | Week 3 of 2024 January 22 To January 28.png | 3 | 9 | Week 9 of 2024 March 04 To March 10.png | |||||||||
| 13 | Start Date | January 22 | 3 | 10 | Week 10 of 2024 March 11 To March 17.png | ||||||||||
| 14 | End Date | January 28 | Week 3 of 2024 January 22 To January 28.png | January 22 | January 28 | 3 | 11 | Week 11 of 2024 March 18 To March 24.png | |||||||
ForecastHistory2024 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:C2,E2 | C2 | =Forecast!E2 |
| H2 | H2 | =IF(A3=$A$1,H1,H1+1) |
| B3 | B3 | =J1 |
| E3,E11,E7 | E3 | =B5 |
| G3,G11,G7 | G3 | =B6 |
| H3,H11,H7 | H3 | =B4 |
| B4,B12,B8 | B4 | =ISOWEEKNUM(C3) |
| C4,C12,C8 | C4 | =C6 |
| H4,H12,H8 | H4 | =IF(A3=$A$1,H3,H3) |
| B5,B13 | B5 | =MAX(DATE(B3,1,1),DATE(B3,1,1)-WEEKDAY(DATE(B3,1,1),2)+(B4)*7+1) |
| H5,H13,H9 | H5 | =IF(A3=$A$1,H4,H4) |
| B6,B14,B10 | B6 | =MIN(DATE(B3+1,1,0),DATE(B3,1,1)-WEEKDAY(DATE(B3,1,1),2)+(B4+1)*7) |
| C6,C14,C10 | C6 | ="Week "&B4&" of "&B3&" "&E6&" To "&G6&".png" |
| B7 | B7 | =J1 |
| C7,C11 | C7 | =C3+7 |
| G6,G14,G10 | G6 | =TEXT($B6,"mmmm dd") |
| H6,H14,H10 | H6 | =IF(A3=$A$1,H5,H5) |
| E6,E14,E10 | E6 | =TEXT($B5,"mmmm dd") |
| B9 | B9 | =MAX(DATE(B7,1,1),DATE(B7,1,1)-WEEKDAY(DATE(B7,1,1),2)+(B8)+7) |
| B11 | B11 | =J1 |
| K4:K14 | K4 | =INDEX($C$3:$C$210,(ROW()-4)*4+4) |
