Repost: Formula Error

"Shannan" wrote:

Oh, but that was only the problem for the one that was giving me a "#NUM!"
error. The one that was giving me "VALUE!" still isn't working.

"Shannan" wrote:

Oh! I figured it out. Someone had accidently typed "2997" as the year in the
first column instead of "1997". But thank you because i didn't know that i
could do custom formatting for dates like that, lol.

"Jacob Skaria" wrote:

Hmm.. Have you tried converting the format to mmm-dd-yyyy to see whether
those dates are displayed as what you intend.

If this post helps click Yes
---------------
Jacob Skaria


"Shannan" wrote:

No, it can't be a formatting problem because all the other rows that have
this same formula have the dates formatted the same and they're working ok.
There's about six rows that are giving me error messages and the rest of the
3000 or so rows are working fine. :S

"Jacob Skaria" wrote:

The dates may not be a valid excel date..

Format the date field to mmm-dd-yyyy to see whether it displays what you
intended.

If this post helps click Yes
---------------
Jacob Skaria


"Shannan" wrote:

Hi,
I'm getting an error message (#NUM!) for the following formula and i'm not
sure why:
=IF(AD31="","",IF(DATEDIF(Z31,AD31,"m")=0,"",DATED IF(Z31,AD31,"m")))

where AD31 has a value of 07-01-05
and Z31 has a value of 97-04-23

This is a formula that i entered into the first row and then dragged the
formula down to apply to the other cells in this column. The formula is
working in most of the other cells.

I aslo got an error message (#VALUE!) for this same formula in a different
row:
=IF(AD74="","",IF(DATEDIF(Z74,AD74,"m")=0,"",DATED IF(Z74,AD74,"m")))

where AD74 is blank
and Z74 is 09-02-04

These two errors occur a couple of times throughout the spreadsheet, while
the rest of the cells with this formula are working fine.

I just cannot figure this out. Row 2226 has the following formula in column
AG:
=IF(AD2226="","",IF(DATEDIF(Z2226,AD2226,"m")=0,"" ,DATEDIF(Z2226,AD2226,"m")))
Where AD2226 is blank (formatted to display as yy-mm-dd) and Z2226 has
04/09/2008 formatted to display as 08-09-04. This gives me the erroe
"VALUE!".

And yet, the row above, 2225 has the same formula in column AG:
=IF(AD2225="","",IF(DATEDIF(Z2225,AD2225,"m")=0,"" ,DATEDIF(Z2225,AD2225,"m")))
Where AD2225 is blank (formatted to display as yy-mm-dd) and Z2225 has
04/03/2008 formatted to display as 08-03-04. And this one is working fine and
stays blank like it should.

"Shannan" wrote:

"Shannan" wrote:

Oh, but that was only the problem for the one that was giving me a "#NUM!"
error. The one that was giving me "VALUE!" still isn't working.

"Shannan" wrote:

Oh! I figured it out. Someone had accidently typed "2997" as the year in the
first column instead of "1997". But thank you because i didn't know that i
could do custom formatting for dates like that, lol.

"Jacob Skaria" wrote:

Hmm.. Have you tried converting the format to mmm-dd-yyyy to see whether
those dates are displayed as what you intend.

If this post helps click Yes
---------------
Jacob Skaria


"Shannan" wrote:

No, it can't be a formatting problem because all the other rows that have
this same formula have the dates formatted the same and they're working ok.
There's about six rows that are giving me error messages and the rest of the
3000 or so rows are working fine. :S

"Jacob Skaria" wrote:

The dates may not be a valid excel date..

Format the date field to mmm-dd-yyyy to see whether it displays what you
intended.

If this post helps click Yes
---------------
Jacob Skaria


"Shannan" wrote:

Hi,
I'm getting an error message (#NUM!) for the following formula and i'm not
sure why:
=IF(AD31="","",IF(DATEDIF(Z31,AD31,"m")=0,"",DATED IF(Z31,AD31,"m")))

where AD31 has a value of 07-01-05
and Z31 has a value of 97-04-23

This is a formula that i entered into the first row and then dragged the
formula down to apply to the other cells in this column. The formula is
working in most of the other cells.

I aslo got an error message (#VALUE!) for this same formula in a different
row:
=IF(AD74="","",IF(DATEDIF(Z74,AD74,"m")=0,"",DATED IF(Z74,AD74,"m")))

where AD74 is blank
and Z74 is 09-02-04

These two errors occur a couple of times throughout the spreadsheet, while
the rest of the cells with this formula are working fine.

Perhaps AD2226 is not blank, but contains spaces? Does =LEN(AD2226) return
zero, or something longer?

If you want to check for AD2226 really having a number, perhaps
=IF(ISNUMBER(AD2226),IF(DATEDIF(Z2226,AD2226,"m")= 0,"",DATEDIF(Z2226,AD2226,"m")),"")--David Biddulph"Shannan" wrote in ...I just cannot figure this out. Row 2226 has the following formula in column AG:=IF(AD2226="","",IF(DATEDIF(Z2226,AD2226,"m")= 0,"",DATEDIF(Z2226,AD2226,"m"))) Where AD2226 is blank (formatted to display as yy-mm-dd) and Z2226 has 04/09/2008 formatted to display as 08-09-04. This gives me the erroe "VALUE!". And yet, the row above, 2225 has the same formula in column AG:=IF(AD2225="","",IF(DATEDIF(Z2225,AD2225,"m")= 0,"",DATEDIF(Z2225,AD2225,"m"))) Where AD2225 is blank (formatted to display as yy-mm-dd) and Z2225 has 04/03/2008 formatted to display as 08-03-04. And this one is working fineand stays blank like it should. "Shannan" wrote: "Shannan" wrote: Oh, but that was only the problem for the one that was giving me a"#NUM!" error. The one that was giving me "VALUE!" still isn't working. "Shannan" wrote: Oh! I figured it out. Someone had accidently typed "2997" as the yearin the first column instead of "1997". But thank you because i didn't knowthat i could do custom formatting for dates like that, lol. "Jacob Skaria" wrote: Hmm.. Have you tried converting the format to mmm-dd-yyyy to seewhether those dates are displayed as what you intend. If this post helps click Yes --------------- Jacob Skaria "Shannan" wrote: No, it can't be a formatting problem because all the other rowsthat have this same formula have the dates formatted the same and they'reworking ok. There's about six rows that are giving me error messages and therest of the 3000 or so rows are working fine. :S "Jacob Skaria" wrote: The dates may not be a valid excel date.. Format the date field to mmm-dd-yyyy to see whether it displayswhat you intended. If this post helps click Yes --------------- Jacob Skaria "Shannan" wrote: Hi, I'm getting an error message (#NUM!) for the following formulaand i'm not sure why: =IF(AD31="","",IF(DATEDIF(Z31,AD31,"m")=0,"",DATE DIF(Z31,AD31,"m"))) where AD31 has a value of 07-01-05 and Z31 has a value of 97-04-23 This is a formula that i entered into the first row and thendragged the formula down to apply to the other cells in this column. Theformula is working in most of the other cells. I aslo got an error message (#VALUE!) for this same formula ina different row: =IF(AD74="","",IF(DATEDIF(Z74,AD74,"m")=0,"",DATE DIF(Z74,AD74,"m"))) where AD74 is blank and Z74 is 09-02-04 These two errors occur a couple of times throughout thespreadsheet, while the rest of the cells with this formula are working fine.

Ooo, yes the "blank" cells did have a space in them! Thanks!

"David Biddulph" wrote:

Perhaps AD2226 is not blank, but contains spaces? Does =LEN(AD2226) return
zero, or something longer?

If you want to check for AD2226 really having a number, perhaps
=IF(ISNUMBER(AD2226),IF(DATEDIF(Z2226,AD2226,"m")= 0,"",DATEDIF(Z2226,AD2226,"m")),"")--David Biddulph"Shannan" wrote in ...I just cannot figure this out. Row 2226 has the following formula in column AG:=IF(AD2226="","",IF(DATEDIF(Z2226,AD2226,"m")= 0,"",DATEDIF(Z2226,AD2226,"m"))) Where AD2226 is blank (formatted to display as yy-mm-dd) and Z2226 has 04/09/2008 formatted to display as 08-09-04. This gives me the erroe "VALUE!". And yet, the row above, 2225 has the same formula in column AG:=IF(AD2225="","",IF(DATEDIF(Z2225,AD2225,"m")= 0,"",DATEDIF(Z2225,AD2225,"m"))) Where AD2225 is blank (formatted to display as yy-mm-dd) and Z2225 has 04/03/2008 formatted to display as 08-03-04. And this one is working fineand stays blank like it should. "Shannan" wrote: "Shannan" wrote: Oh, but that was only the problem for the one that was giving me a"#NUM!" error. The one
that was giving me "VALUE!" still isn't working. "Shannan" wrote: Oh! I figured it out. Someone had accidently typed "2997" as the yearin the first column instead of "1997". But thank you because i didn't knowthat i could do custom formatting for dates like that, lol. "Jacob Skaria" wrote: Hmm.. Have you tried converting the format to mmm-dd-yyyy to seewhether those dates are displayed as what you intend. If this post helps click Yes --------------- Jacob Skaria "Shannan" wrote: No, it can't be a formatting problem because all the other rowsthat have this same formula have the dates formatted the same and they'reworking ok. There's about six rows that are giving me error messages and therest of the 3000 or so rows are working fine. :S "Jacob Skaria" wrote: The dates may not be a valid excel date.. Format the
date field to mmm-dd-yyyy to see whether it displayswhat you intended. If this post helps click Yes --------------- Jacob Skaria "Shannan" wrote: Hi, I'm getting an error message (#NUM!) for the following formulaand i'm not sure why: =IF(AD31="","",IF(DATEDIF(Z31,AD31,"m")=0,"",DATE DIF(Z31,AD31,"m"))) where AD31 has a value of 07-01-05 and Z31 has a value of 97-04-23 This is a formula that i entered into the first row and thendragged the formula down to apply to the other cells in this column. Theformula is working in most of the other cells. I aslo got an error message (#VALUE!) for this same formula ina different row: =IF(AD74="","",IF(DATEDIF(Z74,AD74,"m")=0,"",DATE DIF(Z74,AD74,"m"))) where
AD74 is blank and Z74 is 09-02-04 These two errors occur a couple of times throughout thespreadsheet, while the rest of the cells with this formula are working fine.