crystal Month Conversion

Hello,

I am trying to create a report and I am having a problem with the month caculation. The data is stored a numeric field called month but the months start at number 1 for January and keep continue on through 12 for December. The next year starts with 13 for January and ends with 24 for December, and then starts with month 25 January ends with 36 December and so on.

I have created a caculation to seperate out the different years, but now I am stuck on how I will caculate the months. I figured in the end I could concatenate year and month to get the desired result.

Does anyone have any suggestions?

Thank you
FeliceFerreerAsked:
Who is Participating?
 
bdreed35Connect With a Mentor Commented:
Ok, so the formula I provided will give you the first day of the month, including the appropriate year:

dateadd("m",{table.month},date(1999,12,1))

This will you to do this in one formula rather than several.
The formula is a date data type, so you can format it how you like.
0
 
bdreed35Commented:
In what year does month 1 start in?
You could use the dateadd function with this:

If the first year is say 2000 then your formula would look like this:

dateadd("m",{table.month},date(1999,12,1))

This would return the first day of the approiate month and year, which you could use in other formulas or record selection
0
 
GJParkerCommented:
This formula will return the correvt calendar month form your db field

@MonthNumber
If {Table.Month} <= 12 Then
   {Table.Month}
Else
   {Table.Monh} Mod 12

Simply concatenate this with your year formula.

HTH

Gary
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mlmccCommented:
To get the month create a formula.

numbervar MonNum;

MonNum := {table.MonthNumber} mod 12;
if MonNum = 0 then
  MonNum := 12;

MonNum

mlmcc


0
 
FeliceFerreerAuthor Commented:
It starts with year Jan 2000 as being  Month 1.
0
 
FeliceFerreerAuthor Commented:
okay now I have converted the months and they are all 1-12 I used this formula

@MonthNumber
If {Table.Month} <= 12 Then
   {Table.Month}
Else
   {Table.Monh} Mod 12

Thank you,

But now I need to know how to change the months to have Jan 2002 print out instead
1 2002

Help
0
 
GJParkerCommented:
Use the method posted by bdreed35 and format the date to display "mmm yyyy"

Gary
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.