MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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...


Then you can use the formula, =VLOOUP('date',A:B,2)
to return the work week where 'date' is a valid date

...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
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".