Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Highlight a date field Red if <= Current date Post Reply Post New Topic
Page  of 2 Next >>
Author Message
canonman11
Newbie
Newbie
Avatar

Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
Quote canonman11 Replybullet Topic: Highlight a date field Red if <= Current date
    Posted: 22 Dec 2015 at 5:53pm
I am trying to highlight a Date field if the date is prior to current date showing it is late. I have tried many variations but nothing is helping. My current formula is below.

IF{@Req Date - Date Format} <= (CurrentDate) then
    crRed
Else
    crBlack

The result is everything is highlighting read now. Please I want to take it another step and have jobs with 5 days to current date turn yellow indicating it is becoming late. I am not sure if it is a formatting issue with my field. My source for the date was not as single date field so I had to put the Month, Date and Year together in order to create a date field. Below is my formula to do that.

Cstr({MGSKDP01.DATREM}, "00") & "/" & Cstr({MGSKDP01.DATRED}, "00") & "/" & Cstr({MGSKDP01.DATREY}, "00")

I hope someone can help. Seemed like an easy thing to do but I guess not.

Tim
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Dec 2015 at 3:04am
first try changing how you are creating the your date field to use DATE(YYYY,MM,DD)

date({MGSKDP01.DATREY},{MGSKDP01.DATREM},{MGSKDP01.DATRED})


Then in your conditional formula use a datediff
if datediff("d",currentdate,{@Req Date})<0 then crred else
if datediff("d",currentdate,{@Req Date}) in 0 to 5 then cryellow
else crblack
IP IP Logged
canonman11
Newbie
Newbie
Avatar

Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
Quote canonman11 Replybullet Posted: 23 Dec 2015 at 3:55am
I understand the logic in the formula but my date field is having problems and it is because the Date field below is basically coming from another system that treats dates as 2 digit numbers so each of the fields below only have two digitals.

date({MGSKDP01.DATREY},{MGSKDP01.DATREM},{MGSKDP01.DATRED})


I tried using this formula to add on the "20" for 2016 but then it will not recognize the field as a date field.

"20" + Cstr({MGSKDP01.DATREY}, "00") & "/" & Cstr({MGSKDP01.DATREM}, "00") & "/" & Cstr({MGSKDP01.DATRED}, "00")

I suspect I need to convert that to a proper date field to work.

Tim
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Dec 2015 at 4:05am
I am assuming that the original values are numeric
try to add 2000 to the year

date((2000 + {MGSKDP01.DATREY}),{MGSKDP01.DATREM},{MGSKDP01.DATRED})
IP IP Logged
canonman11
Newbie
Newbie
Avatar

Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
Quote canonman11 Replybullet Posted: 23 Dec 2015 at 7:25am
Awesome worked perfectly.. Many thanks for the help this has been bothering me for quite some time. I appreciate the help again.

Tim

IP IP Logged
canonman11
Newbie
Newbie
Avatar

Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
Quote canonman11 Replybullet Posted: 23 Dec 2015 at 8:42am
I had two date fields I did this with and the data is the same formatting but I get this error message with the second date field when I go to refresh the data. "A month number must be between 1 and 12."

I have checked it with the working field and do not see an issue? I also disabled each by itself and the first one worked but the second one had an error again.


Working one:
date((2000 + {MGSKDP01.DATSCY}),{MGSKDP01.DATSCM},{MGSKDP01.DATSCD})

Bad one:
date((2000 + {MGSKDP01.DATREY}),{MGSKDP01.DATREM},{MGSKDP01.DATRED})

Almost there.

Tim

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Dec 2015 at 9:25am
the date() function expects the values in order (y,m,d)
if DATREM is the day values it will break it.
if DATREM has any value in it that is not between 1 and 12 it will break it.
You can do some defensive coding but you have to choose what you want to do with 'bad data'.
IP IP Logged
canonman11
Newbie
Newbie
Avatar

Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
Quote canonman11 Replybullet Posted: 23 Dec 2015 at 10:35am
I looked at the data and the only thing I am seeing is 0.00 should I ignore those maybe? Otherwise everything appears to be 1 - 12 on the month field.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Dec 2015 at 11:15am
up to you how you want to handle them.
Filtering them out is fine if that works for your overall report need
You an check each element as being valid and exclude on the result or you can use an if then inside the formula to take a different action per row.
IP IP Logged
canonman11
Newbie
Newbie
Avatar

Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
Quote canonman11 Replybullet Posted: 23 Dec 2015 at 11:34am
I browsed both date fields and both are exact 0 - 12 for both date fields.

I am not sure how to handle filtering out anything not from 1-12. Do I make it apart of the condition formula using an If then statement of sorts or on the formula that creates the Date field?? The Scheduled date is working fine so the data must be clean I would suspect. The error specifically was referring to the month but could it be on of the other fields for Day or Year?

I am not sure how to check each element to see if they are valid seems a bit involved for sure.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.