| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
