Jun 02 2021 02:06 PM
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 enough with WEEKNUM (using 15-Friday as the first day of the week) or ISOWEEKNUM (but the default is week starts on Monday). The issue arises when the calendar flips.
The attached file shows a formula I believe is very close in column L but it fails when the calendar flips to a year that starts on Friday (i.e. 2021).
Any guidance or help you can provide would be much appreciated – and probably put hair back in my head an years on my life. Thanks!
Jun 02 2021 03:14 PM
Jun 02 2021 03:45 PM
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 |
Jun 03 2021 02:41 AM
Please check this update
=MOD(ISOWEEKNUM(E4-WEEKDAY(E4,15)),53)+(ISOWEEKNUM(DATE(YEAR(E4),1,1))=53)
Jun 03 2021 11:01 AM
Jun 07 2021 11:22 AM
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 |
Jun 07 2021 11:36 PM