[newbie] first day of week

[Jon]

1. I would like to return the first calendar day of a week number and the
last calendar day of a week number, any ideas?

For example, I have week numbers 11 and 12 and I would like to return

- the first Monday of week 11
- the last Sunday of week 12

2. Any idea if it is possible to keep a 'table' of holidays for working days?

 

[OssieMac]

Hi Jon,

I suggest that you set up the following as a test in a new workbook and see
if it returns the info that you want.

Assume the following:-

Cell A2 contains the date Jan 1 2009

Cell B2 contains any date in 2009.

Cell C2 contains the following formula to calculate the week number with Mon
as the first day of the week:
=WEEKNUM(B2,2)

Cell D2 contains the following formula to calculate date of 1st day of week
number:-
=(C2-1)*7+$A$2-WEEKDAY($A$2)+2

Cell E2 contains the following formula to calculate date of last day of week
number:-
=(C2-1)*7+$A$2-WEEKDAY($A$2)+8

Note that I have made A2 in the formula absolute with the $ signs. this will
allow you to insert Jan 1 2009 in A2 then insert the other formulas in B2,
C2, D2 and E2 and then Autofill (drag) the formulas in those cells down and
you will be able to view the results for other dates.

I suggest that you format all DATE columns as ddd mmm dd yyyy so that you
can view the days of the week with the dates. Column C needs to be formatted
as a number so you can see the week number.

Someone else might be able to simplify the formula for you but it appears to
return the correct results under test.

 

[OssieMac]

My apologies Jon. I forgot to answer your second question.

Lookup NETWORKDAYS function. I think you will find sufficient info there to
create your holidays table and be able to calculate work days. (Assuming that
is what you need.)

 

[Sheeloo]

Tr
=DATE(YEAR(TODAY()),1,1)+CHOOSE(WEEKDAY(DATE(YEAR(TODAY()),1,1),2),1,-1,-2,-3,-4,-5,2)+(B1-1)*7

with weeknumber in B1 for Mond... add 6 to get Sun

For week 1/53... it will may give last/first Monday of previous/next year...