Calculating number of weeks for current month

[Former Member]

I know this must be a simple task but I am pulling my hair out trying to figure it out!

I am writing a monthly invoice report on invoice date for the current month. I have grouped on invoice date per week, however rather than show the invoice date I would like to show 'Week #: 1', 'Week#: 2', etc.

I have only been able to come up with the week number for the year...how do I tweak this formula?

DatePart("ww", DateAdd("d", -1, {TableName.DateField}))

Thanks for any help you can provide!

Marlene Allen

Crystal Reports 2008

CR Developer

Version 12.3.0.601

Product Type: FULL

[Former Member · Accepted solution]

So are you looking for the week of the year or the week the month?

Do you want todays date (5/10/2011) to be week 20 or week 2?

Also are you basing the report off of linked tables or SQL Command?

Edited by: Jason Long on May 10, 2011 2:06 PM

[Former Member · Accepted solution]

I am looking for week number of the month. I am actually working with data from last month in order to see a full month's worth of data to check my formulas. So far I have been able to write this formula which is not working consistently:

datediff('w',dateserial(year(currentdate),month(currentdate)-1,1),)

[Former Member · Accepted solution]

Try it like this...

DatePart("ww", {TableName.Dates}) -
DatePart("ww", DateAdd("m", DateDiff("m", #1/1/1900#, {TableName.Dates}), #1/1/1900#)) + 1

or if you want it all spelled out...

"Week " &
ToText(DatePart("ww", {TableName.Dates}) -
DatePart("ww", DateAdd("m", DateDiff("m", #1/1/1900#, {TableName.Dates}), #1/1/1900#)) + 1, "0") &
" of " &
MonthName(DatePart("m", {TableName.Dates})) &
" " &
ToText(DatePart("yyyy", {TableName.Dates}), "0")

HTH,

Jason

[Former Member · Accepted solution]

Sorry, I didn't answer your other question. Report is based on SQL command.

[Former Member · Accepted solution]

YAY!!! OMG you are so awesome! Thank you so much...I would never have figured this out on my own. I just love this forum.

THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!! THANK YOU !!!

MARLENE

[Former Member · Accepted solution]

The solution I provided works fine in CR regardless. There is a slightly simpler solution in SQL Server, but if I remember correctly, you are using Progress Open Edge... I don't know the syntax to be able to put the logic in the command anyway.

If I'm wrong and you're working with SQL Server and would rather put the logic in the command instead, just let me know.

Jason

[Former Member · Accepted solution]

Haha... In that case you are WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!! WELCOME!!!

[Former Member · Accepted solution]

You're absolutely right...I am using Progress and I am just fine with how this worked out. Thanks so much for your help! I was able to complete the report for my boss' meeting in the morning....silly little thing like week number was keeping me from meeting my deadline.

YOU'RE MY HERO!!!

Have a great evening