Formula to Get week Number starting on Wednesday 2

drom

Active Member
Joined
Mar 20, 2005
Messages
456
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Hi and thanks in advance!

This question is different than the previous one (looks like the desired values were wrong withing the previous post)
  • In my last question (SOLVED)
    • Question
    • Anyway I am gonna use this formula as well


Now I would like to get the Formula In C2 for the Following table
  • In our company the week number goes from Wednesday to Tuesday


Check the Following table:
Row/ColABC
1DATEDesired WeekNumberFormula?
22020-Dec-23 Wednesday52
32020-Dec-24 Thursday52
42020-Dec-25 Friday52
52020-Dec-26 Saturday52
62020-Dec-27 Sunday52
72020-Dec-28 Monday52
82020-Dec-29 Tuesday52
92020-Dec-30 Wednesday1
102020-Dec-31 Thursday1
112021-Jan-01 Friday1
122021-Jan-02 Saturday1
132021-Jan-03 Sunday1
142021-Jan-04 Monday1
152021-Jan-05 Tuesday1
162021-Jan-06 Wednesday2
172021-Jan-07 Thursday2

I would like to have a formula in C1 to get the Week number I put manually in Column B
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MrMisster

New Member
Joined
Feb 21, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
This should do it, tested it on a sheet until April 2022 and it seems to align.

Excel Formula:
=IF(WEEKNUM(A2,13)=53,1,WEEKNUM(A2,13))
 

Forum statistics

Threads
1,141,068
Messages
5,704,099
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top