How to get the week no for the month?

[#1]

Hai,

I want to get the Week no for the month not the year. Please refer the below example and do the needful.

Year Month Week

2009 Jan 1
2009 Jan 2
2009 Jan 3
2009 Jan 4
2009 Feb 1
2009 Feb 2
2009 Feb 3
2009 Feb 4
2009 Mar 1
2009 Mar 2
2009 Mar 3
2009 Mar 4

Thanks in advance...

[#2]

Hi, I did same trouble.

example..

Load XYMD,

Year(XYMD),

Month(XYMD),

Num( Week(XYMD) ) - Week( MonthStart(XYMD) ) + 1 as Weeks,

.............

Or Edit Expression,

=Num(XWeekNum)-Week( MonthStart(XYMD) ) + 1

[prieper]

What would you defined as first week in the month?
The week starting with the 1st of the month, the first Sunday in a month? or with a similar formula as used for the first week of the year (count only, if there are more than 4 days falling into the week)?

Peter

[johnw]

As Peter's question suggests, this is a complicated issue that depends entirely on what you MEAN by the week of the month.

To start with, what exactly do you want displayed for the following example dates?

December 28, 2008 -> ?
December 29, 2008 -> ?
January 1, 2009 -> ?
January 3, 2009 -> ?
January 4, 2009 -> ?
January 5, 2009 -> ?
March 29, 2009 -> ?
March 30, 2009 -> ?
April 1, 2009 -> ?
April 4, 2009 -> ?
April 5, 2009 -> ?

As for sky777a's expression, it would return these answers:

December 28, 2008 -> 4
December 29, 2008 -> -47
January 1, 2009 -> 1
January 3, 2009 -> 1
January 4, 2009 -> 1
January 5, 2009 -> 2
March 29, 2009 -> 5
March 30, 2009 -> 6
April 1, 2009 -> 1
April 4, 2009 -> 1
April 5, 2009 -> 1

Notice the -47 result. That demonstrates one of the potential pitfalls for this question.

Also, do you want ONLY the week number as a separate field like this? Or did you want to have the year and month as part of the field, like 2009 Jan 1. I was unclear from how the question was written.

[#5]

Hai,

Actually I want to display the weeks in the following way.

For Example

Day 1st to 7^th as 1,

Day 8th to 14^th as 2,

Day 15^th to 21^st as 3,

Day 22^nd to 28^th as 4,

Day 29^th to MonthEnd as 5

I written an expression like

set vweek= if( day([inv-date]) >=1 and day([inv-date]) <= 7,1,

if( day([inv-date]) >=8 and day([inv-date]) <= 14,2,

if( day([inv-date]) >=15 and day([inv-date]) <= 21 ,3,

if( day([inv-date]) >=22 and day([inv-date]) <= 28 ,4,5)))) ;

This solved my problem…

Thanks for all of your support…

[#6]

that if's gonna fail in 2 weeks...

[#7]

ok...instead of just criticizing, I've decided to give this a try.

I've attached a QV with a vbscript (that I copied from some site) which you can use with your load script. It may need some tweaking to work with your regional settings and what you consider the first day of the week (sunday or monday) but with my settings, it works.

[johnw]

dragonauta wrote:
that if's gonna fail in 2 weeks... <div></div>

Perhaps I'm missing something, but I see no way for that expression to fail. You can't have more than 31 days in a month, so it should always return a week number. That said, you could get the same result using the much simpler:

ceil(day(inv-date)/7)

[#9]

John Witherspoon wrote:
<pre>
dragonauta wrote:
that if's gonna fail in 2 weeks... <div></div>

Perhaps I'm missing something, but I see no way for that expression to fail. You can't have more than 31 days in a month, so it should always return a week number. That said, you could get the same result using the much simpler:

ceil(day(inv-date)/7)

You can't see how it would fail? Then I'm not understanding something. Take this first part:

if( day([inv-date]) >=1 and day([inv-date]) <= 7,1,

and apply it to 5-Oct-2009. It would return that date being in the 1st week of October, when it would be the 1st day of the 2nd week.

[johnw]

Day(5-Oct-2009) would return 5. 5 >=1 and <=7, so the expression would return 1. That is what was requested:

"Day 1st to 7th as 1,
Day 8th to 14th as 2,
Day 15th to 21st as 3,
Day 22nd to 28th as 4,
Day 29th to MonthEnd as 5"

I wouldn't normally report weeks this way - I'd normally be using Sunday through Saturday (USA). But the given expression will return the requested result.