Remember Me?

#1
October 16th 07, 10:04 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 14
Week of the Month

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,

#2
October 16th 07, 10:12 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,440
Week of the Month

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

#3
October 16th 07, 10:13 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 3,268
Week of the Month

=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,

#4
October 16th 07, 10:14 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 427
Week of the Month

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?

#5
October 16th 07, 10:15 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,397
Week of the Month

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,

#6
October 16th 07, 10:58 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 14
Week of the Month

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,

#7
October 16th 07, 11:00 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 14
Week of the Month

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

#8
October 16th 07, 11:29 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 14
Week of the Month

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?

#9
October 16th 07, 11:51 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2007 Posts: 14
Week of the Month

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?

#10
October 17th 07, 12:01 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 3,268
Week of the Month

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

=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,

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Emilio S. Excel Worksheet Functions 4 October 4th 07 04:24 AM ceemo Excel Discussion (Misc queries) 6 August 1st 06 08:43 PM Monica Hall Excel Discussion (Misc queries) 5 January 9th 06 11:11 PM ikin Charts and Charting in Excel 2 January 16th 05 05:54 PM ankman Excel Worksheet Functions 2 November 24th 04 01:27 AM

All times are GMT +1. The time now is 11:10 PM.