Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Dates
Hi,
I have the following formula to subtract the dates from two different columns: =IF(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m ")) What i want the formula to do is to calculate the differnce between the two dates. However, if there is nothing entered into the columns, then i want the column that's calculating the difference to remain blank. And this is working great. Until i enter in a date into Z11 but not into AD11. Then it gives me the "=NUM!" error instead of remaining blank. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Dates
Adding another IF statement to check if AD11 = "" will keep your "=NUM" from
appearing..... =IF(AD11="","",If(DATEDIF(Z11,AD11,"m")=0,"",DATED IF(Z11,AD11,"m"))) "Shannan" wrote: Hi, I have the following formula to subtract the dates from two different columns: =IF(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m ")) What i want the formula to do is to calculate the differnce between the two dates. However, if there is nothing entered into the columns, then i want the column that's calculating the difference to remain blank. And this is working great. Until i enter in a date into Z11 but not into AD11. Then it gives me the "=NUM!" error instead of remaining blank. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtracting Dates
Hi Shannan,
You could use: =IF(OR(Z11="",AD11=""),0,DATEDIF(Z11,AD11,"m")) and format to the cell with: 0;-0; via Format|Cells|Number|Custom. This way, if either Z11 or AD11 is empty or has a 0 in it, or the months difference is zero, no result will be displayed. -- Cheers macropod [Microsoft MVP - Word] "Shannan" wrote in message ... Hi, I have the following formula to subtract the dates from two different columns: =IF(DATEDIF(Z11,AD11,"m")=0,"",DATEDIF(Z11,AD11,"m ")) What i want the formula to do is to calculate the differnce between the two dates. However, if there is nothing entered into the columns, then i want the column that's calculating the difference to remain blank. And this is working great. Until i enter in a date into Z11 but not into AD11. Then it gives me the "=NUM!" error instead of remaining blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting Dates | Excel Discussion (Misc queries) | |||
Subtracting Dates | Excel Worksheet Functions | |||
Subtracting Dates | Excel Discussion (Misc queries) | |||
Subtracting Dates | Excel Discussion (Misc queries) | |||
Subtracting Dates | Excel Discussion (Misc queries) |