WorkWeek
Posted by Collin Fellows on September 17, 2001 8:34 AM
Has anyone ever created a formula to deal with workweeks? I am looking for a way to enter a date and be able to get a calculation of the workweek and one to be able to enter a workweek and get the corresponding daterange (m-f) I have a calendar with all of the workweeks in column "A" and the days in column "B through H"
Posted by Collin Fellows on September 17, 2001 8:54 AM
The other unique aspect of this is that we do not necessarily use the first full week of a year as workweek one. Sometimes WW1 will start in Dec of the prev year. I found a prev post with the formula
=DATE(Sheet2!A2,ROUND(Sheet2!A1/4,0),1)+IF(2 < WEEKDAY(DATE(Sheet2!A2,ROUND(Sheet2!A1/4,0),1)),7-WEEKDAY(DATE(Sheet2!A2,ROUND(Sheet2!A1/4,0),1))+2,2-WEEKDAY(DATE(Sheet2!A2,ROUND(Sheet2!A1/4,0),1)))
which is very close but a week off for 2002)
Posted by Mark W. on September 17, 2001 8:58 AM
I'd recommend that your reconsider your approach...
If you have a list in columns A:B as follows...
1/1/01,200101
1/7/01,200102
1/14/01,200103
...
12/23/01,200152
Then you can use the formula, =VLOOUP('date',A:B,2)
to return the work week where 'date' is a valid date
value.
...And, the array formula...
{=OFFSET(B1,MATCH('work week',B:B,0)-1,-1,2,1)+{0;-1}}
will produce a vertical array containing the begin
dates of a given 'work week'.
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
Posted by IML on September 17, 2001 10:17 AM
Not sure I fully follow but assuming your date is in A1
For the monday, you can use
=A1-WEEKDAY(A1,2)+1
If you put that in A2, Tuesday would be =a2+1 etc.
If you want the to enter one day and see the work week in one cell, try
=TEXT(A1-WEEKDAY(A1,2)+1,"mmm d/yy")&" - "&TEXT(A1-WEEKDAY(A1,2)+5,"mmm d/yy")
Good luck
Posted by Mark W. on September 17, 2001 11:29 AM
Typo Fix...
Posted by Collin Fellows on September 17, 2001 11:49 AM
Re: Typo Fix...
I caught that one. Thanks a bunch. Neither of them was exactly right for my situation but both were close enough that I think that i am on the right track now. I did have a question on the Double set of brackets on the second suggestion and what did you mean about Shift+Ctrl+Enter
Posted by Mark W. on September 17, 2001 11:57 AM
Array formulae...
The second formula was an array formula... If you're
unfamiliar with them take a look a the Excel Help
topics for "About array formulas and how to enter them"
and "Enter an array formula".
