?
Solved

crystal Month Conversion

Posted on 2005-04-15
7
Medium Priority
?
279 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:FeliceFerreer
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Expert Comment

by:bdreed35
ID: 13791117
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
 
LVL 19

Expert Comment

by:GJParker
ID: 13791133
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 13791165
To get the month create a formula.

numbervar MonNum;

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

MonNum

mlmcc


0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 

Author Comment

by:FeliceFerreer
ID: 13791364
It starts with year Jan 2000 as being  Month 1.
0
 
LVL 28

Accepted Solution

by:
bdreed35 earned 1000 total points
ID: 13791401
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
 

Author Comment

by:FeliceFerreer
ID: 13791469
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
 
LVL 19

Expert Comment

by:GJParker
ID: 13791507
Use the method posted by bdreed35 and format the date to display "mmm yyyy"

Gary
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question