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

MrExcel MVP, Junior Admin
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

MrExcel MVP, Junior Admin
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

MrExcel MVP, Junior Admin
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
 

Joe4

MrExcel MVP, Junior Admin
You haven't told us what your expected value is.
What percentage do you expect to see in this example?
 
Top