#1   Report Post  
Old October 16th 07, 10:04 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 14
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default Week of the Month

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,




  #4   Report Post  
Old October 16th 07, 10:14 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 427
Default 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   Report Post  
Old October 16th 07, 10:15 PM posted to microsoft.public.excel.misc
bj bj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,397
Default 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   Report Post  
Old October 16th 07, 10:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 14
Default 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   Report Post  
Old October 16th 07, 11:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 14
Default 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   Report Post  
Old October 16th 07, 11:29 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 14
Default 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   Report Post  
Old October 16th 07, 11:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 14
Default 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   Report Post  
Old 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
Default Week of the Month

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,






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Week Of Month Emilio S. Excel Worksheet Functions 4 October 4th 07 04:24 AM
Whats the Week of the Month? ceemo Excel Discussion (Misc queries) 6 August 1st 06 08:43 PM
PivotTable Group by Week and Month Monica Hall Excel Discussion (Misc queries) 5 January 9th 06 11:11 PM
how to get week number in month in excel ? ikin Charts and Charting in Excel 2 January 16th 05 05:54 PM
calculate month from week number ankman Excel Worksheet Functions 2 November 24th 04 01:27 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017