|
datedif problem
Hi All
I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras |
datedif problem
Hi
=IF(AND(AG44<"",AG44<=1),"",DATEDIF(T44,IF(AG44=" ",T45,TODAY()) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "bill gras" wrote in message ... Hi All I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras |
datedif problem
bill gras Wrote: Hi All I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras Hi bill Try this =IF(AG441,SUM(TODAY()-T44),SUM(T44-T45)) -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=480035 |
datedif problem
On Fri, 28 Oct 2005 02:07:12 -0500, Paul Sheppard
wrote: bill gras Wrote: Hi All I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras Hi bill Try this =IF(AG441,SUM(TODAY()-T44),SUM(T44-T45)) Your use of the SUM function adds two unnecessary functions to your formula. The same result ensues from: =IF(AG441,TODAY()-T44,T44-T45) --ron |
datedif problem
Hi Arvi
Thank you for your imput Your formula does not work for me , it tels me that a ) or a , is missing so I tried to adjust it but all I get is a #num! error bill -- bill gras "Arvi Laanemets" wrote: Hi =IF(AND(AG44<"",AG44<=1),"",DATEDIF(T44,IF(AG44=" ",T45,TODAY()) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "bill gras" wrote in message ... Hi All I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras |
datedif problem
Hi Ron
Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras -- bill gras "Ron Rosenfeld" wrote: On Fri, 28 Oct 2005 02:07:12 -0500, Paul Sheppard wrote: bill gras Wrote: Hi All I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras Hi bill Try this =IF(AG441,SUM(TODAY()-T44),SUM(T44-T45)) Your use of the SUM function adds two unnecessary functions to your formula. The same result ensues from: =IF(AG441,TODAY()-T44,T44-T45) --ron |
datedif problem
Hi Paul
Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras "Paul Sheppard" wrote: bill gras Wrote: Hi All I need a date difference with a certain condition : If AG44 is greater than 1 give me the date difference between T44 and TODAY(), if AG44 is blank (has a formula that returns a blank cell) than give me the date difference between T44 and T45 and that needs to be coppied down to 3000 rows can some one please help regards bill -- bill gras Hi bill Try this =IF(AG441,SUM(TODAY()-T44),SUM(T44-T45)) -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=480035 |
datedif problem
Hi Bill
I think there was a typo in Arvi's formula and it should read =IF(AND(AG44<"",AG44<=1),"",DATEDIF(T44,IF(AG44=" ",T45,TODAY()))) Regards Roger Govier bill gras wrote: Hi Arvi Thank you for your imput Your formula does not work for me , it tels me that a ) or a , is missing so I tried to adjust it but all I get is a #num! error bill |
datedif problem
On Fri, 28 Oct 2005 05:21:08 -0700, bill gras
wrote: Hi Ron Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras It's not my formula, but I don't understand what you are saying. =IF(AG441,TODAY()-T44,T44-T45) Did you actually try it? If AG44 is blank, it will return a zero which, in my world, is less than 1. Therefore the logical_test evaluates to FALSE, and the value_if_false will be returned. Here are some sample runs: T44: 15 Feb 2005 T45: 19 Feb 2005 AG44: <blank -- -4 (difference between T44-T45) AG44: 2 -- 255 (difference between T44 and TODAY()) --ron |
datedif problem
On Fri, 28 Oct 2005 05:21:08 -0700, bill gras
wrote: Hi Ron Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras In addition to my previous post, your specification had to do with AG44 being blank. A blank cell has nothing in it. It is EMPTY. If AG44 has something in it, then it is NOT BLANK. (see HELP for the ISBLANK worksheet function) For example, if AG44 has a formula in it, then it has a formula in it. Containing a formula that returns a null string is NOT the same as a BLANK cell. If that is the case, you need to change the logical_test to account for the actual conditions. If, for example, you have a formula in the cell that is returning a null string, e.g. "", then the difference formula needs to take that into account: So, what exactly is in AG44 ??? --ron |
datedif problem
On Fri, 28 Oct 2005 09:46:53 -0400, Ron Rosenfeld
wrote: On Fri, 28 Oct 2005 05:21:08 -0700, bill gras wrote: Hi Ron Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras In addition to my previous post, your specification had to do with AG44 being blank. A blank cell has nothing in it. It is EMPTY. If AG44 has something in it, then it is NOT BLANK. (see HELP for the ISBLANK worksheet function) For example, if AG44 has a formula in it, then it has a formula in it. Containing a formula that returns a null string is NOT the same as a BLANK cell. If that is the case, you need to change the logical_test to account for the actual conditions. If, for example, you have a formula in the cell that is returning a null string, e.g. "", then the difference formula needs to take that into account: So, what exactly is in AG44 ??? --ron Finally, I reread your specification. And, indeed, you do have a formula in there. My post was actually just in reference to Paul's formula and I had not looked at the source posting closely, being distracted by the use of BLANK. My apologies. Modifying that formula, and assuming that the formula in AG44 returns a null string ("") (not a blank), then: =IF(AG44="",T44-T45,IF(AG441,TODAY()-T44,"")) --ron |
datedif problem
Hi Ron
your formula works fine , but as cell AG44 is blank as a result of a work sheet function I can only get the result of the difference between AG44 and TODAY() I tried your formula with an empty cell (no formula) and no problem it works fine. This is the formula that is in cells AG44 to AG3000 it is an array formula every so many rows there is number that is greater than 1 and the rest are blank using the belowe function =IF(OR(ISNUMBER(AF43),ISBLANK(AF44)),"",AVERAGE(LA RGE(AF44:INDEX(AF45:AF$65536,MATCH(TRUE,ISBLANK(AF 45:AF$65536),0)),ROW(INDIRECT("1:"&MIN(5,MATCH(TRU E,ISBLANK(AF45:AF$65536),0))))))) regards bill -- bill gras "Ron Rosenfeld" wrote: On Fri, 28 Oct 2005 05:21:08 -0700, bill gras wrote: Hi Ron Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras It's not my formula, but I don't understand what you are saying. =IF(AG441,TODAY()-T44,T44-T45) Did you actually try it? If AG44 is blank, it will return a zero which, in my world, is less than 1. Therefore the logical_test evaluates to FALSE, and the value_if_false will be returned. Here are some sample runs: T44: 15 Feb 2005 T45: 19 Feb 2005 AG44: <blank -- -4 (difference between T44-T45) AG44: 2 -- 255 (difference between T44 and TODAY()) --ron |
datedif problem
On Fri, 28 Oct 2005 07:11:03 -0700, bill gras
wrote: Hi Ron your formula works fine , but as cell AG44 is blank as a result of a work sheet function I can only get the result of the difference between AG44 and TODAY() I tried your formula with an empty cell (no formula) and no problem it works fine. This is the formula that is in cells AG44 to AG3000 it is an array formula every so many rows there is number that is greater than 1 and the rest are blank using the belowe function =IF(OR(ISNUMBER(AF43),ISBLANK(AF44)),"",AVERAGE(L ARGE(AF44:INDEX(AF45:AF$65536,MATCH(TRUE,ISBLANK(A F45:AF$65536),0)),ROW(INDIRECT("1:"&MIN(5,MATCH(TR UE,ISBLANK(AF45:AF$65536),0))))))) regards bill See my subsequent postings for the answer. I got confused because in Excel, "BLANK" is a defined word meaning the same as EMPTY. --ron |
datedif problem
Hi Ron
Thank you for your reply and effort the formula works great You are a gentle man ! regards bill -- bill gras "Ron Rosenfeld" wrote: On Fri, 28 Oct 2005 09:46:53 -0400, Ron Rosenfeld wrote: On Fri, 28 Oct 2005 05:21:08 -0700, bill gras wrote: Hi Ron Your formula only returns Datedif between Today()-T44 and does not return the difference between T44-T45 if AG44 is blank bill -- bill gras In addition to my previous post, your specification had to do with AG44 being blank. A blank cell has nothing in it. It is EMPTY. If AG44 has something in it, then it is NOT BLANK. (see HELP for the ISBLANK worksheet function) For example, if AG44 has a formula in it, then it has a formula in it. Containing a formula that returns a null string is NOT the same as a BLANK cell. If that is the case, you need to change the logical_test to account for the actual conditions. If, for example, you have a formula in the cell that is returning a null string, e.g. "", then the difference formula needs to take that into account: So, what exactly is in AG44 ??? --ron Finally, I reread your specification. And, indeed, you do have a formula in there. My post was actually just in reference to Paul's formula and I had not looked at the source posting closely, being distracted by the use of BLANK. My apologies. Modifying that formula, and assuming that the formula in AG44 returns a null string ("") (not a blank), then: =IF(AG44="",T44-T45,IF(AG441,TODAY()-T44,"")) --ron |
datedif problem
On Fri, 28 Oct 2005 15:51:03 -0700, bill gras
wrote: Hi Ron Thank you for your reply and effort the formula works great You are a gentle man ! regards bill -- bill gras Well, I'm happy we arrived at a satisfactory solution to your problem. Thank you for the feedback. Best, --ron |
| All times are GMT +1. The time now is 02:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com