Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula doesnt work
I will be putting dates into cells in column A and B. I will use Row1 as
the reference for my question. If I have dates in A1 and B1 I need to calculate the number of intervening months - I used =Datedif(a1,b1,"m"). If both cells are empty, I the number zero is returned. If one cell is empty, I get #Num. If both are populated, I get the correct answer. I want to supress both the 0 and the #num. I tried =if(iserror(and(isblank(a1),isblank(b1)),"",datedi f((a1,b1,m)),"",if(and(isblank(a1),isblank(b1)),"" ,datedif((a1,b1,m))) It doesnt work, and I can not figure out what is wrong. Please help. |
#2
|
|||
|
|||
One way
=IF(OR(A1="",B1=""),"",DATEDIF(A1,B1,"m")) -- Regards, Peo Sjoblom "Kevin" <ksorei at yahoo.com wrote in message ... I will be putting dates into cells in column A and B. I will use Row1 as the reference for my question. If I have dates in A1 and B1 I need to calculate the number of intervening months - I used =Datedif(a1,b1,"m"). If both cells are empty, I the number zero is returned. If one cell is empty, I get #Num. If both are populated, I get the correct answer. I want to supress both the 0 and the #num. I tried =if(iserror(and(isblank(a1),isblank(b1)),"",datedi f((a1,b1,m)),"",if(and(isblank(a1),isblank(b1)),"" ,datedif((a1,b1,m))) It doesnt work, and I can not figure out what is wrong. Please help. |
#3
|
|||
|
|||
it was indeed my logic. Switching from And to Or makes the world a
wonderful place. Thank you "Peo Sjoblom" wrote: One way =IF(OR(A1="",B1=""),"",DATEDIF(A1,B1,"m")) -- Regards, Peo Sjoblom "Kevin" <ksorei at yahoo.com wrote in message ... I will be putting dates into cells in column A and B. I will use Row1 as the reference for my question. If I have dates in A1 and B1 I need to calculate the number of intervening months - I used =Datedif(a1,b1,"m"). If both cells are empty, I the number zero is returned. If one cell is empty, I get #Num. If both are populated, I get the correct answer. I want to supress both the 0 and the #num. I tried =if(iserror(and(isblank(a1),isblank(b1)),"",datedi f((a1,b1,m)),"",if(and(isblank(a1),isblank(b1)),"" ,datedif((a1,b1,m))) It doesnt work, and I can not figure out what is wrong. Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
4 Day Work Week in a formula | Excel Discussion (Misc queries) | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions | |||
formula from Excel97 doesn't work in Excel2003,any ideas why? | Excel Worksheet Functions |