If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Repost: Formula Error
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. |
#3
|
|||
|
|||
Repost: Formula Error
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. |
#4
|
|||
|
|||
Repost: Formula Error
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. |
Thread Tools | |
Display Modes | |
|
|