AND, MAX

  • Thread starter Thread starter PAL
  • Start date Start date
P

PAL

I am working with the formula below. G2 and I2 are dates, or blank. If both
are blank, I would like a blank in the field, if one or both have dates, I
would like the latest date.

=IF(AND(G2,I2)="","",MAX(G2,I2))

Please advise why I get the #Value! error.

Thanks.
 
=IF(AND(ISBLANK(G5),ISBLANK(I5)),"",MAX(G5,I5))

inside the AND complete two logical tests
 
=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error
Click to expand...

As written, your formula needs to be array entered so AND will evaluate
*all* of its arguments.

However, you don't need an array formula to do this. Try it like this:

=IF(COUNT(G2,I2),MAX(G2,I2),"")
 
Thanks. It produces blanks in all cases. That is blanks regardless of
whether there are no dates, 1 date or 2 dates. Could this be a format issue?
The dates are formatted as 01-jan-2000.
 
Thanks. When I use this, the value that comes out when a date is appropriate
is 0-jan-oooo. Is it a formating issue.
 
Could this be a format issue?
The dates are formatted as 01-jan-2000.
Click to expand...

Then your dates aren't true Excel dates. They're probably TEXT strings that
look like dates. If you have what you think are dates in any of those cells
then:

=COUNT(G2,I2)

Will return a number other than 0.

Since you're only dealing with 2 cells try this:

Select the cells in question
Goto the menu Format>Cells>Number tab
Select GENERAL
OK

Manually re-enter the dates in those cells.

Basically, what you're doing is resetting the format *but* it won't reset
until you edit the cell by re-entering the dates.
 
Yes, that works. I initially tried to reformat the cells, but that didn't do
it. Retyping did. The data is from an export and there are a few hundred
lines. The retyping part isn't practical.
 
Try opening and closing TTC.

Select the column of dates in question, then, from the Menu Bar,
<Data> <Text To Columns> <Finish>

This should make all those dates XL "Legal".

If this doesn't work, you might have imbedded invisible characters from the
web.

Post back if that's the case.
 
You can try this:

Select the range of cells in question
Goto the menu Data>Text to Columns
Click Next>Next
In step 3, select DATE>MDY>Finish

Sometimes that will convert text dates to true Excel dates.
 
Back
Top