Newbie Questions

Re: traffic light system based on 2 dates

Testman1962 — Fri, 24 Jun 2011 18:00:52 GMT

Hi Jimmy,

many thanks for your help, I have found a way around this from another entry on this site. It calculates the difference between 2 dates and out puts this into another field I then take this value and use some conditional formatting to get my traffic light to work.

Re: traffic light system based on 2 dates

Jimmy — Wed, 22 Jun 2011 14:52:58 GMT

The only way around the lack of the AddDays function is to write code that does something similar. It would be some fairly simple code that calculates the number of days between today and the specified date, place that value in a field somewhere, and then use that number for your conditional formatting. It's our general policy not to write code for people on this forum, but if you're willing to avail yourself to Qdabra's consulting services, we can work up a solution for you at a reasonable price.

Re: traffic light system based on 2 dates

Testman1962 — Wed, 22 Jun 2011 07:39:43 GMT

Hi Jimmy

I need it to conform to the same rules, ie if <7 days green / 7 to 30 days amber and >30 days red. Even if someone puts the data into the form with a date in the past. This will be used for a overview of live set of information. I could supply my original spreadsheet with the conditioal formating or I could post the rules for you to look at. I think the addition of the drop down selection " YES / NO " sould only be an additional rule with and "AND" condition. ?

I've just tried this on work system and as they only supply me with MS IP 2003 "AddDays" is not supported ! AHHHHHH
Is there any way around this ? sorry to be a right royal pain !

Re: traffic light system based on 2 dates

Jimmy — Wed, 22 Jun 2011 03:00:26 GMT

What color do you want it to be if it's in the past?

Re: traffic light system based on 2 dates

Testman1962 — Tue, 21 Jun 2011 21:40:30 GMT

Hi Jimmy

many thanks for the quick responce, I've tried it out and it works. But I've tried to put in a date in the past and it stays green !

What I'm trying to do is to have the traffic lights compare what ever date you put in with today and it will conform to the traffic lights.I need it to update every time you open the form. Also I need to have another field with a " YES / NO " dropdown list and if it's "NO" it works on the traffic lights , but if it's "YES" the cell stays white.

can this be done in infopath ?

many thanks

Kevan

Re: traffic light system based on 2 dates

Jimmy — Mon, 20 Jun 2011 15:50:17 GMT

Something like this should work:

Condition

translate(field, ":-T", "") <= translate(xdDate:AddDays(xdDate:Today(), 7), ":-T", "")

Shade green

Condition

translate(field, ":-T", "") > translate(xdDate:AddDays(xdDate:Today(), 7), ":-T", "") AND translate(field, ":-T", "") <= translate(xdDate:AddDays(xdDate:Today(),30), ":-T", "")

Shade amber

Condition

translate(field, ":-T", "") > translate(xdDate:AddDays(xdDate:Today(), 30), ":-T", "")

Shade red

traffic light system based on 2 dates

Testman1962 — Mon, 20 Jun 2011 14:04:52 GMT

I need to setup a form with a traffic light indicator. I am using MS IP 2003.

one date would be from a date picker and compared with todays date (in a repeating table)

<= 7 ( days) GREEN

8 to 30 (days) AMBER

>30 (days) RED

I have managed it in excel 2003 using conditional formatting, but I can't seem to find a way in MS IP, I think this may require some code.

can someone help ?