lost_in_the_sauce
Board Regular
- Joined
- Jan 18, 2021
- Messages
- 128
- Office Version
- 365
- Platform
- Windows
One of our forecasting models needs an overhaul and the current system breaks monthly expenses by category down weekly except it needs an end value for each month, so it throws some weeks off.
Example:
Week 1 Jan1-7
Week 2 Jan 8-14
Week 3 Jan 15-21
Week 4 Jan 22-28
Week 5 Jan 29-31 (report)
Week 6 Feb 1-4
Week 7 Feb 5-12
etc
I'm swapping it to a daily breakout to try and smooth the variances between weeks out but I'm trying to create a formula for which week to pull an expense amount from before dividing it by the days in that week, so that if cell A1 has 1/1/2021, cell B1 has 1/2/2021, cell C1 has 1/3/2021, etc - then the cells directly below in row 2 would have 1/1/2021 though 1/1/2021 and then 1/14/2021 for the next 7, etc (end of week dating for expenses).
Example:
Week 1 Jan1-7
Week 2 Jan 8-14
Week 3 Jan 15-21
Week 4 Jan 22-28
Week 5 Jan 29-31 (report)
Week 6 Feb 1-4
Week 7 Feb 5-12
etc
I'm swapping it to a daily breakout to try and smooth the variances between weeks out but I'm trying to create a formula for which week to pull an expense amount from before dividing it by the days in that week, so that if cell A1 has 1/1/2021, cell B1 has 1/2/2021, cell C1 has 1/3/2021, etc - then the cells directly below in row 2 would have 1/1/2021 though 1/1/2021 and then 1/14/2021 for the next 7, etc (end of week dating for expenses).