datedif problem

B

bill gras

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
 
P

Paul Sheppard

bill said:
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
Click to expand...

Hi bill

Try this >

=IF(AG44>1,SUM(TODAY()-T44),SUM(T44-T45))
 
R

Ron Rosenfeld

Hi bill

Try this >

=IF(AG44>1,SUM(TODAY()-T44),SUM(T44-T45))
Click to expand...

Your use of the SUM function adds two unnecessary functions to your formula.
The same result ensues from:

=IF(AG44>1,TODAY()-T44,T44-T45)


--ron
 
B

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
 
B

bill gras

Hi Ron
Your formula only returns Datedif between Today()-T44 and does not
return the difference between T44-T45 if AG44 is blank

bill
 
B

bill gras

Hi Paul
Your formula only returns Datedif between Today()-T44 and does not
return the difference between T44-T45 if AG44 is blank

bill
 
R

Roger Govier

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
 
R

Ron Rosenfeld

Hi Ron
Your formula only returns Datedif between Today()-T44 and does not
return the difference between T44-T45 if AG44 is blank

bill
Click to expand...

It's not my formula, but I don't understand what you are saying.

=IF(AG44>1,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
 
R

Ron Rosenfeld

Hi Ron
Your formula only returns Datedif between Today()-T44 and does not
return the difference between T44-T45 if AG44 is blank

bill
Click to expand...


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
 
R

Ron Rosenfeld

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
Click to expand...

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(AG44>1,TODAY()-T44,""))





--ron
 
B

bill gras

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(LARGE(AF44:INDEX(AF45:AF$65536,MATCH(TRUE,ISBLANK(AF45:AF$65536),0)),ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(AF45:AF$65536),0)))))))

regards bill
 
R

Ron Rosenfeld

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(LARGE(AF44:INDEX(AF45:AF$65536,MATCH(TRUE,ISBLANK(AF45:AF$65536),0)),ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(AF45:AF$65536),0)))))))

regards bill
Click to expand...


See my subsequent postings for the answer.

I got confused because in Excel, "BLANK" is a defined word meaning the same as
EMPTY.


--ron
 
B

bill gras

Hi Ron
Thank you for your reply and effort the formula works great
You are a gentle man !

regards bill
 
R

Ron Rosenfeld

Hi Ron
Thank you for your reply and effort the formula works great
You are a gentle man !

regards bill
Click to expand...

Well, I'm happy we arrived at a satisfactory solution to your problem. Thank
you for the feedback.

Best,

--ron
 
Top