if statement

I have the following formula:

if(date-TODAY()<=7,"Payment due","")

The problem is when today's date falls after the first date the result is a
negative number and it returns "payment due" since it's less than 7 even
though the payment has been made. A solution (since I'm a new user, not
likely to be the best one) might be specifying a range of from 1-7 days but I
don't know how to do that.

Hi Brian,
As asked
=IF(AND(date-TODAY()<=7, date<TODAY()),"Payment due","")

You would be better off checking a cell or flag to tell if the payment
has been made for this period in addition or before checking the
difference. .
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BrianM" <[email protected]> wrote in message news:[email protected]...
> I have the following formula:
>
> if(date-TODAY()<=7,"Payment due","")
>
> The problem is when today's date falls after the first date the result is a
> negative number and it returns "payment due" since it's less than 7 even
> though the payment has been made. A solution (since I'm a new user, not
> likely to be the best one) might be specifying a range of from 1-7 days but I
> don't know how to do that.

Yes, that would be good but I have an array of 9 rows representing 9
payment-due dates. Complicating matters a bit is the fact that there are ten
other spreadsheets that have the same issue. Each spreadsheet (each one
representing an owned property) links to a summary of owned properties. I
want to create a link from each property spreadscheet to the summary
spreadsheet which, among other things, shows that a payment is coming up. By
the way, payment dates are different for each so having this kind of alert
would be quite useful.

Speaking or the array. the perfect formula would loop from each payment
date - TODAY() pair and report whether a payment was due. Since I'm only
working with one column of 9 cells and, if I understand how arrays work, I
need another column to make the array formula work, that doesn't appear to be
an option.

Thank you for your suggestion...
"David McRitchie" wrote:

> Hi Brian,
> As asked
> =IF(AND(date-TODAY()<=7, date<TODAY()),"Payment due","")
>
> You would be better off checking a cell or flag to tell if the payment
> has been made for this period in addition or before checking the
> difference. .
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "BrianM" <[email protected]> wrote in message news:[email protected]...
> > I have the following formula:
> >
> > if(date-TODAY()<=7,"Payment due","")
> >
> > The problem is when today's date falls after the first date the result is a
> > negative number and it returns "payment due" since it's less than 7 even
> > though the payment has been made. A solution (since I'm a new user, not
> > likely to be the best one) might be specifying a range of from 1-7 days but I
> > don't know how to do that.
>
>
>