Logical functions with dates and TODAY()

[Frustrated]

An afternoon project for someone!!

I am trying to create a spreadsheet which tracks and records assignments not
completed by the assigned due date. I need:

1. Dates to be entered as 20100101 but show up as 2010.01.01 AND
2. Use the TODAY() function to determine (and show) that, as of today:
a) an item is not yet due - leave cell blank
b) an items is outstanding past the assigned due date - show "Overdue"
c) an item is/was completed on or before the due date - show "Completed"
d) an item is/was completed AFTER the due date - show "Completed Late"
3. To be able to sort by result (e.g. Overdue, Completed etc.)

I HOPE there's a way to have the spreadsheet look something like:
-------------------------------------------------------------
E.g. Today's Date - January 31, 2010

Due Date Date Completed (Desired Result)
2010.01.07 2010.01.05 Completed
2010.02.15
2010.01.05 Overdue
2010.01.05 2010.01.15 Completed Late

 

[Mike H]

Hi,

I think this covers it

=IF(AND(B2="",OR(A2="",A2>TODAY())),"",IF(AND(B2="",A2<TODAY()),"Overdue",IF(B2>A2,"Completed Late",IF(AND(B2<>"",B2<=A2),"Completed"))))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

I forgot your date format.

use a custom format of
yyyy.mm.dd

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Frustrated]

Thank you sooooooooooo much. It works PERFECTLY.

 

[Frustrated]

Fantastic! Thanks again. One more question? Is there any way I can
restrict the entry for the date to be e.g. 20100101 and NOT let someone enter
the decimals themselves (e.g. NOT let them enter 2010.01.01)? It seems the
sorting etc. won't work when the decimals are entered - changes it from a
date to text I think?

 

[Mike H]

Glad I could help. You would have to resort to VB to have a date entered
like that and even then I think you would have a lot of difficulty trapping
all errors.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Frustrated]

You've been a godsend! Thank you again so very much for your help.

 

[Rick Rothstein]

Actually, I don't think trapping the errors in VB would be that hard;
basically, something like this should work (assuming it is in an Event
procedure)...

.....
.....
CellValue = Replace(Target.Value, ".", "")
If CellValue Like "########" Then Target.Value = Format(Target.Value,
"0000.00.00")
.....
.....

 

[CellShocked]

Glad I could help. You would have to resort to VB to have a date entered
like that and even then I think you would have a lot of difficulty trapping
all errors.

I made a drop down validation list off to the side that goes like:

=TODAY()-5
=TODAY()-4
=TODAY()-3
=TODAY()-2
=TODAY()-1
=TODAY()-0
=TODAY()+1

Etc.

Then, I name that highlighted list of cells as a named range of say
"DayList" then set the validation to "=DayList". Then, they are
restricted to those dates, and the list advances as time advances.

edit as desired, but the format of the target cells will determine what
it looks like if you use this drop down.

 

[CellShocked]

Actually, I don't think trapping the errors in VB would be that hard;
basically, something like this should work (assuming it is in an Event
procedure)...

....
....
CellValue = Replace(Target.Value, ".", "")
If CellValue Like "########" Then Target.Value = Format(Target.Value,
"0000.00.00")
....
....

I think simply giving a drop down list of dates, and locking the entry
that way will insure it. Then the cell formatting "displays" it however
you desire. See my other post.