Hi,

Can someone help me with a formula that determines which week of the month a
specific date falls. i.e. 15/10/07 = Week 3

Thanks,

It depends on what system of week numbering you'd like to use.

Look at this page of Ron de Bruin's site; you'll probably be able to get to a solution from there

http://www.rondebruin.nl/weeknumber.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Janet BN" wrote in message ...
| Hi,
|
| Can someone help me with a formula that determines which week of the month a
| specific date falls. i.e. 15/10/07 = Week 3
|
| Thanks,
|
|

With your date in A1

=CEILING(DAY(A1)/7,1)




--


Regards,


Peo Sjoblom



"Janet BN" wrote in message
...
Hi,

Can someone help me with a formula that determines which week of the month
a
specific date falls. i.e. 15/10/07 = Week 3

Thanks,

This is trickier than it sounds, because a month can start on a Friday
or Saturday- does the first of that month constitute week one? How do
you define a week?

how do you define when the first week of the month starts,

if it is a work week, is week 1 the week that contains 1 or the first full
week in the month?

does your week start on Monday or sunday?

If it starts on the first no matter what day of the week it is,
=ceiling(A1/7,1)
for a date in A1
"Janet BN" wrote:

Hi,

Can someone help me with a formula that determines which week of the month a
specific date falls. i.e. 15/10/07 = Week 3

Thanks,

Thanks for this bj - we work a 7 day calendar so the first week of the month
is the one that contains the 1st day of the month. Though I couldn't seem to
make this work, comes up with a figure of 5367 for date 24/08/06, when I was
really just looking for 4.


"bj" wrote:

how do you define when the first week of the month starts,

if it is a work week, is week 1 the week that contains 1 or the first full
week in the month?

does your week start on Monday or sunday?

If it starts on the first no matter what day of the week it is,
=ceiling(A1/7,1)
for a date in A1
"Janet BN" wrote:

Hi,

Can someone help me with a formula that determines which week of the month a
specific date falls. i.e. 15/10/07 = Week 3

Thanks,

Hi Niek, thanks for this. However, this seems to return the week of the
year, not month.

"Niek Otten" wrote:

It depends on what system of week numbering you'd like to use.

Look at this page of Ron de Bruin's site; you'll probably be able to get to a solution from there

http://www.rondebruin.nl/weeknumber.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Janet BN" wrote in message ...
| Hi,
|
| Can someone help me with a formula that determines which week of the month a
| specific date falls. i.e. 15/10/07 = Week 3
|
| Thanks,
|
|

Hi Dave,

We work a 7 day week and the 1st week of the month is the one that contains
the 1st day of the month.



"Dave O" wrote:

This is trickier than it sounds, because a month can start on a Friday
or Saturday- does the first of that month constitute week one? How do
you define a week?

Hi Dave,
We have a 7 days per week schedule so the 1st week of the month can fall on
any day and is the one that contains the 1st day of the month.

Thanks
"Dave O" wrote:

This is trickier than it sounds, because a month can start on a Friday
or Saturday- does the first of that month constitute week one? How do
you define a week?

You obviously missed my answer which does what you want, just remember to
format the result as general

"With your date in A1

=CEILING(DAY(A1)/7,1)"


--


Regards,


Peo Sjoblom


"Janet BN" wrote in message
...
Thanks for this bj - we work a 7 day calendar so the first week of the
month
is the one that contains the 1st day of the month. Though I couldn't seem
to
make this work, comes up with a figure of 5367 for date 24/08/06, when I
was
really just looking for 4.


"bj" wrote:

how do you define when the first week of the month starts,

if it is a work week, is week 1 the week that contains 1 or the first
full
week in the month?

does your week start on Monday or sunday?

If it starts on the first no matter what day of the week it is,
=ceiling(A1/7,1)
for a date in A1
"Janet BN" wrote:

Hi,

Can someone help me with a formula that determines which week of the
month a
specific date falls. i.e. 15/10/07 = Week 3

Thanks,