AND, MAX
 

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.

AND, MAX
 

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

inside the AND complete two logical tests

"PAL" wrote:

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.

AND, MAX
 

=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error

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),"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

AND, MAX
 

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.

"T. Valko" wrote:

=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error

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),"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

AND, MAX
 

Thanks. When I use this, the value that comes out when a date is appropriate
is 0-jan-oooo. Is it a formating issue.

"klswvu" wrote:

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

inside the AND complete two logical tests

"PAL" wrote:

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.

AND, MAX
 

Could this be a format issue?
The dates are formatted as 01-jan-2000.

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 FormatCellsNumber 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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

"T. Valko" wrote:

=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error

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),"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

AND, MAX
 

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.

"T. Valko" wrote:

Could this be a format issue?
The dates are formatted as 01-jan-2000.

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 FormatCellsNumber 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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

"T. Valko" wrote:

=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error

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),"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

AND, MAX
 

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PAL" wrote in message
...
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.

"T. Valko" wrote:

Could this be a format issue?
The dates are formatted as 01-jan-2000.

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 FormatCellsNumber 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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

"T. Valko" wrote:

=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error

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),"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

AND, MAX
 

You can try this:

Select the range of cells in question
Goto the menu DataText to Columns
Click NextNext
In step 3, select DATEMDYFinish

Sometimes that will convert text dates to true Excel dates.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

"T. Valko" wrote:

Could this be a format issue?
The dates are formatted as 01-jan-2000.

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 FormatCellsNumber 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.


--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.

"T. Valko" wrote:

=IF(AND(G2,I2)="","",MAX(G2,I2))
Please advise why I get the #Value! error

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),"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
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.