|
| 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
October 9th, 2009, 05:31 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Error
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
October 9th, 2009, 05:40 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Error
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
October 9th, 2009, 05:47 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Error
Your formulas work ok for me when I use dates as per my regional settings:
07-01-05 = 1/5/2007 (Jan 5 2007) 97-04-23 = 4/23/1997 (Apr 23 1997) You'll get a #NUM! error if the date in AD31 is before the date in Z31. You'll get a #VALUE! error if any date is really a TEXT string (that may look like a date). -- Biff Microsoft Excel MVP "Shannan" wrote in message ... 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.
|
|
#4
October 9th, 2009, 05:50 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Error
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.
|
|
#5
October 9th, 2009, 06:02 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Error
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.
|
|
#6
October 9th, 2009, 07:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Error
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.
|
|
#7
October 9th, 2009, 07:13 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Error
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
