# Date calculating as a number in formula

#### Dummy Excel

##### Well-known Member
Hi All,
i have a formula which im trying to calculate a percentage based on historical data by week
Formula:
Code:
``=(Networks!\$C\$62-COUNTIFS(Historical!\$A:\$A,Q\$16,Historical!\$I:\$I,WEEKNUM((TODAY())-P27)))/Networks!\$C\$62``
I believe the formula is correct, although when i "evaluate" the formula it changes weeknum to a number (43761) which i understand why, although thats where the problem is.
"WEEKNUM((TODAY())-P27)" becomes (43761-42) as P27=WEEKNUM(TODAY())-1) to get the previous week number

So the formula evaluates to 3719 which equals to wk 37 and not wk 42

HELP!
thanks
Sam

#### Joe4

If you are doing what I think you are, I believe you have your parentheses in the wrong spot. You are subtracting 42 from the date, then taking the Week Number of that, when I think what you want to do is take the Week Number of the current date, and then subtract 42 from that week number.

So, your formula should look like this:
WEEKNUM(TODAY())-P27
or this:
(WEEKNUM(TODAY())-P27)

#### Anthony47

##### Well-known Member
In short, you are asking how to do a calculation that your formula doesn't return, without saying what you want to calculate and without showing how the data are organized...
The only thing I can guess is that you should use WEEKNUM(TODAY())-P27) rather than WEEKNUM((TODAY())-P27))

Or you should explain what, that portion of the formula, should return

Bye

#### Dummy Excel

##### Well-known Member
In short, you are asking how to do a calculation that your formula doesn't return, without saying what you want to calculate and without showing how the data are organized...
The only thing I can guess is that you should use WEEKNUM(TODAY())-P27) rather than WEEKNUM((TODAY())-P27))

Or you should explain what, that portion of the formula, should return

Bye
Hi Anthony, sorry you are right.
that part of the formula is trying to basically workout the "current week number" minus "last weeks week number".
im trying to build a table with 52 weeks of data to provide a weekly percentage

does that make sense?

#### Joe4

Did you see my reply/try my suggestion?
I think it should solve your issue, and I explained what you did wrong.

Last edited:

#### Dummy Excel

##### Well-known Member
Did you see my reply/try my suggestion?
I think it should solve your issue, and I explained what you did wrong.
Hi Joe4
yes sorry, got side tracked and haven't had chance to reply although yes thats correct
im getting 11900% as the result which obviously isn't right!
i think its got to do with the parentheses as well although couldn't work it out

#### Joe4

I haven't analyzed your formula, just the one part that you mentioned that you were having issues with.
Let's break the formula down into parts, and see what each part returns:
Code:
``````=Networks!\$C\$62
=COUNTIFS(Historical!\$A:\$A,Q\$16,Historical!\$I:\$I,WEEKNUM(TODAY())-P27)``````
What do each of those formulas return?

What is the value you expect to get from your data?

Last edited:

#### Dummy Excel

##### Well-known Member
Networks!\$C\$62 = 119 (total number of entries)
Historical!\$A:\$A = North (our territory in the raw data)
Q\$16 = North (territory in the table)
Historical!\$I:\$I = count of data for North for availability
WEEKNUM(TODAY()) = current week ie 43
P27 = 42

#### Anthony47

##### Well-known Member
that part of the formula is trying to basically workout the "current week number" minus "last weeks week number"
In this case you should use WEEKNUM(TODAY())-P27), ie:
Code:
``=(Networks!\$C\$62-COUNTIFS(Historical!\$A:\$A,Q\$16,Historical!\$I:\$I,WEEKNUM(TODAY())-P27))/Networks!\$C\$62``
But I don't know what is in Historical!\$I:\$I, cannot say if the formula is logically correct or not
Also I seem that "current week number" minus "last weeks week number" will always be "1", to add uncertainty to the formula adopted

Bye