Conditional formatting based on four date ranges

[bssltd]

Hi,

I'm trying to add a "traffic light" conditional format based on four separate date cells for a plant hire schedule.
These four cells are Date On, Date Off, Date Suspended From, and Date Suspended To.

What I want to achieve is:
- A Green light if today's date is greater than or equal to the Date On cell and not greater than the Date Off cell, indicating the plant is ON hire.
- An Amber light if today's date is equal to the same formula as the green light, but also greater than or equal to the Suspended From date, and not greater than the Suspended To date, indicating the plant is SUSPENDED.
- A Red light if today's date is outside the Date On and Date Off cells, indicating the plant is OFF hire.

The Date Off cell will remain blank until the plant is off hired.
The Suspended To date will also remain blank until the end of the suspension period.

I've tried various methods of achieving this.
I've tried the Traffic light function within one cell, as well as trying three different cells - one for each status.
I've tried inputting formula in 3 separate cells to return the values 0,1, or 2 depending on status.
So far I've played about with IF functions for the On/Off dates along with AND to add the Suspended dates.

I'm not that experienced with more complex formulae/functions, and don't know what else may work.

Here's an example of my date columns, and what should be their current status.
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Current Status[/TD]
[TD]Hire On[/TD]
[TD]Hire Off[/TD]
[TD]Suspended From[/TD]
[TD]Suspended To[/TD]
[/TR]
[TR]
[TD]*Green*[/TD]
[TD]01/07/2014[/TD]
[TD][/TD]
[TD]12/07/2014[/TD]
[TD]15/07/2014[/TD]
[/TR]
[TR]
[TD]*Red*[/TD]
[TD]20/07/2014[/TD]
[TD]25/07/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]*Amber*[/TD]
[TD]20/07/2014[/TD]
[TD][/TD]
[TD]28/07/2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated.
Sorry for the long post, and I hope I've explained myself clearly.

Thanks in advance.

 

[adam087]

I think this calculation will work, which you can then translate in to Conditional Formatting and test for; but I may well have missed something so try it with a range of data...

Excel 2010

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Current Status[/TD]
[TD="align: center"]Status Calc[/TD]
[TD="align: center"]Hire On[/TD]
[TD="align: center"]Hire Off[/TD]
[TD="align: center"]Suspended From[/TD]
[TD="align: center"]Suspended To[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]*Green*[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]01-Jul-14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12-Jul-14[/TD]
[TD="align: center"]15-Jul-14[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]*Red*[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]20-Jul-14[/TD]
[TD="align: center"]25-Jul-14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]*Amber*[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]20-Jul-14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]28-Jul-14[/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(AND(TODAY()>C2,OR(TODAY()<d2,d2=""< font="">)</d2,d2=""<>), IF(OR(AND(E2="", F2=""), AND(TODAY()>F2, F2<>"")), "G", "A"), "R")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

/AJ

 

[bssltd]

Thanks for that Adam.
I've tweaked it a little, but your suggestion was a good starting point for me. The only bit that's missing is the Off Hire information. Your formula doesn't cover it, and I'm struggling to see how to integrate it with what you've done already.

Any more suggestions?

Thanks,
Chris

(Sorry for the late response, but I've not had a lot of time to look at this over the last week).