| Author |
Message |
canonman11
Newbie
Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
|
   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 Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
canonman11
Newbie
Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
canonman11
Newbie
Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
|
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 Logged |
|
canonman11
Newbie
Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
canonman11
Newbie
Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
canonman11
Newbie
Joined: 10 May 2011
Location: United States
Online Status: Offline
Posts: 10
|
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 Logged |
|
|
|
