Need a Function

[akemeny]

I currently have the following function calculating:

=IF(OR(AA7="",AD7<>""),"",TODAY()-(AA7+65))

AA7 and AD7 are date columns. The column they calculate in is AC7. So if
AA7 is zero then AC7 is zero. If AA7 has a date then AC7 should equal
AA7+65-the current date. But if AD7 has a date then AC7 should equal AD7-AA7
instead of calculating from the first portion.

With the current function its only placing a zero when AD7 has a date. How
can I modify the current function so that I also calculates correctly??

 

[Ashish Mathur]

Hi,

Try this =if(AA7="","",TODAY()-(AA7+65),if(AD7="","",(AD7-AA7)))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[akemeny]

It didn't work. Excel stated that the formula had too many arguements for
the function.

 

[Ashish Mathur]

Hi,

Sorry about that. Hope this helps.

if(AA7<>"",TODAY()-(AA7+65),if(AD7<>"",AD7-AA7,""))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[akemeny]

That one didn't return an error, but it only calculates the first portion
regarding Column AA. I think it needs an or option because the way I can
explain it is:

if AA7 equals zero then AC7 equals zero, but if AA7 equals a date then AC7
equals Today minus AA7 plus 65. However, if AD7 equals a date then the
previous formula no longer calculates, instead AC7 would now equal AD7 minus
AA7.

 

[Ashish Mathur]

if(and(AA7<>"",AD7=""),TODAY()-(AA7+65),if(and(AD7<>"",AD7<>""),AD7-AA7,0))

Hope this helps.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[akemeny]

That one worked perfectly.

Thanks!

if(and(AA7<>"",AD7=""),TODAY()-(AA7+65),if(and(AD7<>"",AD7<>""),AD7-AA7,0))

Hope this helps.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Thank you for the feedback

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com