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


=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

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

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

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

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

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

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

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

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

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

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