Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Week Of Month | Excel Worksheet Functions | |||
Whats the Week of the Month? | Excel Discussion (Misc queries) | |||
PivotTable Group by Week and Month | Excel Discussion (Misc queries) | |||
how to get week number in month in excel ? | Charts and Charting in Excel | |||
calculate month from week number | Excel Worksheet Functions |