Forum Discussion
Return the starting date of a fiscal week
Hi, I'm new to Excel. I want to include a column with values of starting date of each week in fiscal week column. How can I achieve the second column using a formula or other method?
First fiscal week is 2022_12 and its starting date is 01/16/22. My idea is to add index for each fiscal week and multiply the index value to 7 days
e.g.
| Week | Index | Date |
| 2022_12 | 1 | 01/16/22 |
| 2022_13 | 2 | 01/16/22 + (7 * 2) |
| 2022_14 | 3 | 01/16/22 + (7 * 3) |
Note: 2022_13 is both 01/23/22
3 Replies
Here are the formulas you can maybe use:
1. To calculate the week number of each date in your data, use this formula:
=INT((A2-DATE(YEAR(A2+7-WEEKDAY(A2-1)),1,1)-WEEKDAY(DATE(YEAR(A2+7-WEEKDAY(A2-1)),1,1),3)+5)/7)
This formula accounts for the NRF 4-5-4 calendar.
2. To add an index for each fiscal week, use this formula:
=IF(WEEKDAY(B2)=1,B2,"")&"-"&TEXT(WEEKNUM(B2),"00")
This formula calculates the difference between each week’s starting date and the starting date of your fiscal year.
3. To multiply the index value by 7 days, simply multiply the cell containing the index value by 7.
I hope that helps!
I know I don't know anything (Socrates)
Hi NikolinoDE. Thanks for your info. The only data I have is the first column (Fiscal Week). What I want to achieve is that start date of the said fiscal week (second column). I put there only to show that these are the results I need.
Please see the attached file for a detailed overview.
=("4.1."&YEAR(A2))+B2*7-7-MOD("2.1."&YEAR(A2);7)
