automaticly increment number by elapsed time

[kimdnw]

My boss has me working a sheet of reports by his subordinates. I compile the
report, number it, then have a series of "Countif" statements that give me
the number of reports submitted by a specific subordinate.

He would now like to see a column of submitted/expected. The expectation is
one per week. Is there an easy way to have a column that increments +1 per
calendar week, so I don't need to go in and type the new number every week
for each subordinate?

Kim

 

[GSnyder]

I'm thinking you can get the expectation by using =Weeknum(now()) as long as
you're OK with it resetting each year and you start with the calendar year.

Would that work, or do you need something a little more sophisticated?

 

[driller]

kim,

You don't need to go in and type the *new number every week*
for each subordinate....

for which *one*---> *"the submitted or the expected ?"*

 

[kimdnw]

You are correct that I am looking for the calendar year week count in the
expectation block. I copied this formula and am receiving "#NAME", and the
internal paranthesis are showing green. Does this mean I need to insert more
data?

Kim

 

[David Biddulph]

Did you look at what Excel help for the WEEKNUM function said, in the
sentence starting "If this function is not available, and returns the #NAME?
error, ..."
?

 

[kimdnw]

Yes, thank you.

Had to get admin to add the "Analysis ToolPak", then it worked. Also, due
to the comments in "Help" added a "-1" to the formula. WEEKNUM counts the
week that includes January 1. Since this year that week was only 2 days, I
don't want it counted as a week.

So the formula as I am using it is "=WEEKNUM(NOW())-1" which today returns
the answer as "9".
Thanks to all for your help.

 

[kimdnw]

The "expected" is the number I was trying to automatically increment. The
"submitted" column is being updated with the "countif" statement.

GSnyder's answer works for the "expected" once I got the "Analysis Toolpak"
loaded as an add-in.

So the "submitted" statement is: =COUNTIF
('datalocation'!E7:E200,"supervisorname")

The "expectation" statement is: =WEEKNUM(NOW())-1

The "-1" removes the first week of January this year, which is only 2 days
long.

Thanks.

Thanks!