Contextual conditional formatting help
Sorry if my subject is a little vague. What I'm trying to do is create a time field that changes color depending on what time it is vs. the time value that's in the field. The field is for a deadline, so if the deadline is 5 PM I want that field to turn red if it's less than one hour away from the deadline (so 4 PM or later), yellow if it's 2 hours away (3-4 PM) or green if it's 3 hours away, etc.
I'm having trouble with the syntax because I'm not good at scripting. I'm setting this up as Conditional Formatting, as a formula. This is what I have so far:
Self < (Get(CurrentTime) + 1) Self < (Get(CurrentTime) + 2) Self < (Get(CurrentTime) + 3)
And for each one I've assigned a different color fill, so red for the first line, yellow for the second one, green for the third. Does this make any sense so far? I'm trying to say "If the current time is less than one hour away from the time in this box, then turn the field red."
I'm not sure if I need to put a specific range in there like "if it's between current time plus 2-3 hours", and "3-4 hours" etc. Or if I'm just going about this all wrong and there's a much easier answer I haven't seen. Any ideas?