Week of the month.

[check75]

Hi

Need to know how can I calculate week of the month of the date given by the user, either in crystal reports or SQL Server. Working with Seagate Crystal Report 7.0.

eg. Date: March 6th 2001.
I need to know its second week of march.

Thanks
Kamal

 

[kenhamady]

When does the first week end?

First Sat?
First Sun?
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[check75]

Saturday. Assuming Mar 1, Mar 2 First week.

March 5th to March 9th Second week.

 

[Nilsen]

If Sunday is the first day of the week, here is a formula you can use:

(Day({table.datefield}) + (7 - DayOfWeek({table.datefield}) - 1)) / 7


If Sunday is not the first day, you may need to tweek it some. THe DayOfWeek function counts sunday to saturday (1 to 7)

 

[Nilsen]

Disregard that first formula, it does not work under any circumstances (unless you round, but it will not always work). I was typing it out of memory while sitting here with a head cold (poor excuse).

Try this formula:

(Day({Table.Date}) + (7 - DayOfWeek({Table.Date}) + DayOfWeek(Date(Year({Table.Date}), Month({Table.Date}), 1)) - 1)) / 7

Again, this will only work if your week starts on Sunday. If it works on a different day, some tweaking will be needed.