Forum Discussion
Count Weeks in Sequential Year
I have been fighting with a formula for way too long and I hope you can help. Our “gas-week” starts on a Friday and goes through Thursday. I want to assign a week number to each date. Seems easy ...
It still gets confused in the second week of 2021. Expanded sample attached
| Date | Day of Week | Week Num |
| 1/1/2021 | Fri | 1 |
| 1/2/2021 | Sat | 1 |
| 1/3/2021 | Sun | 1 |
| 1/4/2021 | Mon | 1 |
| 1/5/2021 | Tue | 1 |
| 1/6/2021 | Wed | 1 |
| 1/7/2021 | Thu | 1 |
| 1/8/2021 | Fri | 1 |
| 1/9/2021 | Sat | 1 |
| 1/10/2021 | Sun | 1 |
| 1/11/2021 | Mon | 1 |
| 1/12/2021 | Tue | 1 |
| 1/13/2021 | Wed | 1 |
| 1/14/2021 | Thu | 1 |
| 1/15/2021 | Fri | 2 |
| 1/16/2021 | Sat | 2 |
| 1/17/2021 | Sun | 2 |
Please check this update
=MOD(ISOWEEKNUM(E4-WEEKDAY(E4,15)),53)+(ISOWEEKNUM(DATE(YEAR(E4),1,1))=53)kcastrofan , great, glad it helped
I apologize for the delayed follow up. But upon further inspection the formula mishandles years where week 53 starts on a Friday - the following partial week is assigned a week 52. It should be a continuation of week 53. Sample file is attached.
Thank you in advance for the follow up.
12/23/2016 Fri 52 12/24/2016 Sat 52 12/25/2016 Sun 52 12/26/2016 Mon 52 12/27/2016 Tue 52 12/28/2016 Wed 52 12/29/2016 Thu 52 12/30/2016 Fri 53 12/31/2016 Sat 53 1/1/2017 Sun 52 1/2/2017 Mon 52 1/3/2017 Tue 52 1/4/2017 Wed 52 1/5/2017 Thu 52 1/6/2017 Fri 1 1/7/2017 Sat 1 1/8/2017 Sun 1 1/9/2017 Mon 1 1/10/2017 Tue 1 1/11/2017 Wed 1 1/12/2017 Thu 1