Repost: Formula Error

  • Thread starter Thread starter Shannan
  • Start date Start date
S

Shannan

:

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.
 
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.
 
Ooo, yes the "blank" cells did have a space in them! Thanks!

David Biddulph said:
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" <[email protected]> wrote in messagenews:[email protected]...>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
Click to expand...
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,"",DATEDIF(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,"",DATEDIF(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.
 
Back
Top