Week Numbers - Again!

[mlv]

I am trying to find a simple formula that will calculate week numbers for
'Sunday to Saturday' weeks from the information in two cells. Because there
seems to be no pattern to when the first week of the year will start, the
Sunday of the first week will be entered manually in Cell A1. Therefore:

Cell A1 will contain the date of the Sunday of the first week of the year,
e.g. 8 April 2007.

Cell B1 will contain today's date, =TODAY()

I think the formula should be quite easy, but I am not sure how to handle
the week 53 issue - if there is a week 53 issue.

It would be good if the formula worked for any week style (e.g. Sunday to
Saturday, Monday to Sunday, etc.), based on the first day of the first week
being entered in Cell A1.

Can anyone help with a formula?

Thanks

 

[JE McGimpsey]

One way:

=INT((B1-A1)/7)+1

 

[Bob Phillips]

JE's date will return week 53, but 54 et al as well.

I would add a check to stop week 54 etc.

=IF(INT((B1-A1)/7)+1>53,"Year start invalid",INT((B1-A1)/7)+1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

True, and if one is going to enter dates more than a year out, it's
probably worth automating the whole thing using one of these techniques:

http://www.cpearson.com/excel/weeknum.htm

 

[mlv]

JE's date will return week 53, but 54 et al as well.

I would add a check to stop week 54 etc.

=IF(INT((B1-A1)/7)+1>53,"Year start invalid",INT((B1-A1)/7)+1)

Thanks guys, that pointed me in the right direction.

I've taken the formula a stage further to consider the date entered in A1
being later than the 'Today' date in B1, thereby giving a negative week
number.

=IF(OR(A1>B1,(INT((B1-A1)/7)+1>53)),"Year Start Invalid",INT((B1-A1)/7)+1)