TheRins3r

New Member
Joined
Dec 12, 2016
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I have a formula below , where I need the week number from 1 - 13 depending on the upload date & what Qtr we are in.



=WEEKNUM(J4058)-WEEKNUM(LOOKUP(J4058,DATE(YEAR(J4058),{3,6,9,12},30)))


When a new QTR starts, the weeknum should be "1", however it keeps populating as "0".

Any help would be appreciated.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
You could also try this:

=IF(MOD(WEEKNUM(J4058),13)=0,13,MOD(WEEKNUM(J4058),13))
 
Upvote 0
You could also try this:

=IF(MOD(WEEKNUM(J4058),13)=0,13,MOD(WEEKNUM(J4058),13))


Unfortunately that did not work, see below example where the new week count starts at 0 and should start at 1

28/06/201928-Jun-192019JUN QtrJune13
01/07/201901-Jul-192019SEP QtrJuly0

<tbody>
</tbody>
 
Upvote 0
That formula gives me 1 not 0
Is the date in J4508 a real date, or text?
 
Upvote 0
This is what I get

Book1
IJ
4101/07/2019
5208/07/2019
6315/07/2019
7422/07/2019
8529/07/2019
9605/08/2019
10712/08/2019
11819/08/2019
12926/08/2019
131002/09/2019
141109/09/2019
151216/09/2019
161323/09/2019
17130/09/2019
Request
Cell Formulas
RangeFormula
I4=IF(MOD(WEEKNUM(J4),13)=0,13,MOD(WEEKNUM(J4),13))
 
Upvote 0
Your formula works, i forgot to change the lookup cells when i pasted it in!!

Thanks very much.
 
Upvote 0
It's not my formula, it's @tyija1995 that supplied it. ;)

But glad it's working & thanks for the feedback
 
Upvote 0
Thanks guys - glad it's working now, have a good day! :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top