Need help with an expression

[SSG Snuggle Bunny]

I was kicked over my task tracker project so they could hand it to somebody else and now somebody else has kicked it back at me because he doesn't know what to do.

Some days the officers make it hard to salute with all 5 fingers.

Anyhoo...

I'm trying to write an expression. It's an If/Then which, for the most part I have a handle on it BUT...

...I need to say if a project is older than 7 days due its condition red.

The thing is, if I write:

COMPLETEDATE > (DUEDATE + 7) = RED

it does nothing for me if the project was completed 8, 9 or more days late.

Conversely, what should I write if the project was completed on or within 6 days of its due date?

[kenth]

I was kicked over my task tracker project so they could hand it to somebody else and now somebody else has kicked it back at me because he doesn't know what to do.

Some days the officers make it hard to salute with all 5 fingers.

Anyhoo...

I'm trying to write an expression. It's an If/Then which, for the most part I have a handle on it BUT...

...I need to say if a project is older than 7 days due its condition red.

The thing is, if I write:

COMPLETEDATE > (DUEDATE + 7) = RED

it does nothing for me if the project was completed 8, 9 or more days late.

Conversely, what should I write if the project was completed on or within 6 days of its due date?

If (not completed) and (currentdate >= duedate + 7) then red alert

You can check for a completed project using a flag of some sort, or if the completion date allows nulls, then just check for a date at all.

Does the tracker need to signify a color level for projects finished after the due date, or just those that aren't yet finished?

[SSG Snuggle Bunny]

VARIABLES:
==================
Field A1: Due date = DUE

Field A2: Completed = DONE
-or-
Field A2: Incomplete = ZERO

Report Date = TODAY

==========================
(DUE + ZERO) ≤ TODAY = GREEN
(DUE + ZERO) > TODAY = AMBER
(DUE + ZERO) > (TODAY + 7) = RED
DONE ≤ DUE = GREEN
DONE > DUE = AMBER
DONE > (DUE + 7) = RED

============================

So what I'm testing for is:

1. What is the due date? (Field A1)

2. Is the project complete or not? (Field A2)

2a. If the project is complete the complete date is entered.

2b. If the project is NOT complete the date field is zero.

Test all incomplete projects against today's date to see if:
-- the are not yet due (GREEN)
-- currently due (AMBER)
-- or late (RED)

Test completed project dates against its due date to see if:
-- it was competed early (GREEN)
-- ontime (AMBER)
-- or late (RED)




I hope this isn't too confusing. I'm an absolute n00b at writing formulas and code. The project is SUPPOSED to be done in excel but I think this is overtaxing that program unless it can be written as a macro or as VBA.

I'm just now flirting with VBA for MS Access but I'm still learning the terminology (I think I'd rather have Jave and mySQL). I futzed around last week and got a few macros to work but just simple stuff like getting a report to email itself to people. I have a hard time cold starting myself. If someone hand carries me over the first hurdle I tend to sel-guide easily enough after that.

[kenth]

You could do it in VBA, or as just an expression in a spreadsheet:

=IF(B3>DATEVALUE("1/1/1900"),IF(B3<=A3,1,IF((B3-A3)<7,2,3)),IF(TODAY()<=A3,1,IF((TODAY()-A3)<7,2,3)))

I made a small sample spreadsheet with due date in column A, completion date in column B, and used a third column for this expression. It returns a 1 for the green value, 2 for amber and 3 for red according to your rules.

The actual colors can be stuck into your cells with conditional formatting. You can either make it so it colors the due date with the conditional formatting, or easier, use the third column as the color column. I'll take a screenshot in a bit.

VBA is much nicer to read though, and returns the same numbers and you could probably use it to change the cell formatting as well without using the conditional formats. The below is pretty clunky, but simply done.

Public Function DateColor(due As Date, done As Date, tod As Date) As Integer
    If done > "1/1/1900" Then
        If done <= due Then
            DateColor = 1
        Else
            If (done - due) < 7 Then
                DateColor = 2
            Else
                DateColor = 3
            End If
        End If
           
    Else
        If tod <= due Then
            DateColor = 1
        Else
            If (due - tod) > 0 Then
                DateColor = 1
            Else
                If (tod - due) < 7 Then
                    DateColor = 2
                Else
                    DateColor = 3
                End If
            End If
        End If
    End If
End Function

Your cell function would just be '=DateColor(DueDateCell, CompletionDateCell, Today())'