Date - Help

[trevor]

Hi All

I am writing a formula to return 3 values, using 2 IF statement.

=IF(TODAY()>A13,"OVERDUE","OPEN"),IF(TODAY()>C13,"CLOSED")
But for some reason it keeps on returning an error message #value!

Cell A13 & C13 contains dates, formmatted!
Do i need to unformat the dates to read better? ie: 38143 = 06/06/2008

Also I have two other cell which can determin the closed date, which you
enter the either a two figure number or another cell with a year date (ie:
2007-2008)

Thanks

 

[pdberger]

Trevor --

You need to construct nested IF statements differently, with each one
usually set as the "if-false" part of the prior one. I can't figure out what
you're trying to accomplish specifically, but it should look something like
this:

=IF(TODAY()>A13,"Overdue",IF(TODAY()>C13,"CLOSED","OPEN"))

HTH

 

[Max]

Your IF construct was not correct
Try this revision:
=IF(COUNT(A13,C13)<2,"",IF(TODAY()>A13,"OVERDUE",IF(TODAY()>C13,"CLOSED","OPEN")))

 

[Reitanos]

You were getting an error because of a slight arrangement issue in
your formula:
=IF(TODAY()>A13,"OVERDUE",IF(TODAY()>C13,"CLOSED","OPEN"))
Remember that the second IF has to be one of the results of the first
IF.

Sorry, but I didn't understand the second part of your question.

 

[trevor]

Hi max

Thanks for your revised statement.
However I did not understand it properly a little confussed!
Especially the count bit!

I am try to auto input the following:
Overdue to appear if the date is after the agreed date
Open to appear if within the agreed date
closed if data is in any of the closed colums (3)
but offten it is entered the 'closed date colum'

I want to appear in the either closed, open or overdue

 

[trevor]

Hi
Thank you for you relpy, I can believe its a little confussing to
understand.(Sometimes I find it difficult!)

However I am trying to develop a system which auto - inputs the following
outputs:
OPEN& OVERDUE are entried in the same column! A13
Closed information is entried in another column C13

For example I have this statement - =IF(TODAY()>Q16, "OVERDUE", "OPEN")
which works (abit), however only for data entered in the cell Q16, but if I
add an if statement for the 'CLOSED' then it does not work!?

eg:=IF(TODAY()>Q16, "OVERDUE", "OPEN", IF(TODAY()>C13,"CLOSED"))) - DOES NOT
WORK!

The statement that you sent was kind of correct, but the open part of the
staement didn't!
=IF(TODAY()>A13,"Overdue",IF(TODAY()>C13,"CLOSED","OPEN")) )Yours! WHICH
WORKS BUT THE OPEN DONT NOT APPEAR!

HOPE U CAN DO YOUR MAGIC!

REGARDS

 

[Max]

The front IF check:
=IF(COUNT(A13,C13)<2,"", ...
is just an added precaution to ensure that *both* A13 and C13 do contain
real dates (real dates are numbers) before proceeding with the rest of the IF
evaluations, otherwise spurious/meaningless results might arise since blank
cell/s are evaluated as zeros in expressions.

For example, if A13 was blank, then: IF(TODAY()>A13 would always evaluate to
TRUE, which would be a spurious/meaningless result, no? Similarly for C13 &
the check: IF(TODAY()>C13.

When you plugged the entire expression in, did it return the expected results?

 

[trevor]

Thank again for your reply, but i'm still getting no joy with this,
Maybe I am explaining it wrongly to you!
Now when I try your statement its outcome is nothing, blank.

But I understand your way abit clearer, however is they any chance of
getting a contact add so I can show what I am doing?

thanks

 

[David Biddulph]

If the outcome of Max's formula is a blank, then you haven't got real dates
in your 2 input cells.
Check your data.