Weeknum

[TheRins3r]

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.

 

[tyija1995]

You could also try this:

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

 

[TheRins3r]

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

[TABLE="width: 535"]
<tbody>[TR]
[TD]28/06/2019[/TD]
[TD]28-Jun-19[/TD]
[TD]2019[/TD]
[TD]JUN Qtr[/TD]
[TD]June[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]01/07/2019[/TD]
[TD]01-Jul-19[/TD]
[TD]2019[/TD]
[TD]SEP Qtr[/TD]
[TD]July[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

That formula gives me 1 not 0
Is the date in J4508 a real date, or text?

 

[TheRins3r]

yes , it is a date.

 

[Fluff]

This is what I get

Book1
	I	J
4	1	01/07/2019
5	2	08/07/2019
6	3	15/07/2019
7	4	22/07/2019
8	5	29/07/2019
9	6	05/08/2019
10	7	12/08/2019
11	8	19/08/2019
12	9	26/08/2019
13	10	02/09/2019
14	11	09/09/2019
15	12	16/09/2019
16	13	23/09/2019
17	1	30/09/2019
Request

Cell Formulas
Range	Formula
I4	=IF(MOD(WEEKNUM(J4),13)=0,13,MOD(WEEKNUM(J4),13))

 

[TheRins3r]

Your formula works, i forgot to change the lookup cells when i pasted it in!!

Thanks very much.

 

[Fluff]

It's not my formula, it's @tyija1995 that supplied it.

But glad it's working & thanks for the feedback

 

[tyija1995]

Thanks guys - glad it's working now, have a good day!