Date query

Posted by Michael Cunningham on July 16, 2001 2:38 PM

I am using excel 2000 for some data analysis. I need to break a list of 20,000 dates into workweeks (Intel workweeks). I could type in the entire calendar for the past 3 years and set each day equal to its corresponding work week number, but I still can't get an "IF" function to work (ie: if "cell x" = "cell y", return work week value in cell z, if not go to next row and run same function, keep repeating until you find a match for the day). Maybe there is an easier way to do this?

Thanks,

Mike

Posted by Mark W. on July 16, 2001 4:34 PM

Michael, create a Work Week table that's keyed on
the start date of each week. Here at Motorola our
Fiscal year always starts on January 1st, weeks
generally (week 1 can be an exception) start on
Sunday, and so our fiscal calendar looks like...

{"Start Date","Work Week"
;"1/1/2001",1
;"1/7/2001",2
;"1/14/2001",3
;"1/21/2001",4
;"1/28/2001",5
;"2/4/2001",6
;"2/11/2001",7
;"2/18/2001",8
;"2/25/2001",9
;"3/4/2001",10
;"3/11/2001",11
;"3/18/2001",12
;"3/25/2001",13
;"4/1/2001",14
;"4/8/2001",15
;"4/15/2001",16
;"4/22/2001",17
;"4/29/2001",18
;"5/6/2001",19
;"5/13/2001",20
;"5/20/2001",21
;"5/27/2001",22
;"6/3/2001",23
;"6/10/2001",24
;"6/17/2001",25
;"6/24/2001",26
;"7/1/2001",27
;"7/8/2001",28
;"7/15/2001",29
;"7/22/2001",30
;"7/29/2001",31
;"8/5/2001",32
;"8/12/2001",33
;"8/19/2001",34
;"8/26/2001",35
;"9/2/2001",36
;"9/9/2001",37
;"9/16/2001",38
;"9/23/2001",39
;"9/30/2001",40
;"10/7/2001",41
;"10/14/2001",42
;"10/21/2001",43
;"10/28/2001",44
;"11/4/2001",45
;"11/11/2001",46
;"11/18/2001",47
;"11/25/2001",48
;"12/2/2001",49
;"12/9/2001",50
;"12/16/2001",51
;"12/23/2001",52}

Note: The dates are shown as text values here to
improve readability. You should use date values
instead.

Now, suppose that I wanted to lookup the work
week for May 23rd. I'd just use the formula,
=VLOOKUP("5/23/2001"+0,$A$2:$B$53,2) which returns
work week 21 even though 5/23/2001 isn't explictly
listed in the table. Give it a try!