IF Statement with Today() for Action Log

[Tashat]

Hello everyone,

I'm hoping someone can help me here. I'm trying to produce a basic action log spreadsheet and I want to add due dates. In the cell alongside due date I want the status to automatically appear with the following logic:
if today's date is less than the due date but not within a week of the due date, then I want it to read "Coming Due"
if today is within a week of the due date I want it to read "Due"
and if today is past the due date, then "Overdue"

So far I have this:

Where A1 is the due date:

<bb>=IF(A1="","",IF(TODAY()<=A1,"Coming Due",IF(TODAY()>A13,"Overdue",)))</bb>

I'm having trouble allowing for the due within a week part of the formula. Any help would be much appreciated.

Natasha

 

[JackDanIce]

Try:

=IF(ISBLANK(A1),"",IF(TODAY()+7<=A1,"Coming due",IF(AND(TODAY()>=A1-7,TODAY()<A1+1),"Due","Overdue")

 

[Tashat]

Brilliant - thank you very much. I will look closely at the formula to understand the logic, but it appears to work. Thanks again.

 

[scottylad2]

<bb></bb>

Excel Workbook
	A	B
1	01/05/2010
2	02/05/2010
3	03/05/2010
4	04/05/2010
5	05/05/2010
6	06/05/2010
7	07/05/2010
8	08/05/2010
9	09/05/2010
10	10/05/2010
11	11/05/2010	coming due
12	12/05/2010	coming due
13	13/05/2010	coming due
14	14/05/2010	coming due
15	15/05/2010	coming due
16	16/05/2010	coming due
17	17/05/2010	coming due
18	18/05/2010	coming due
19	19/05/2010	past Due
20	20/05/2010	past Due
21	21/05/2010	past Due
Sheet1

 

[JackDanIce]

You're welcome - to help you:

1 Check if the cell's blank
2 If not
3 Check if today's date + 7 days is less than the due date (i.e. more than a week away)
4 If it is then it's before the due date and thus "coming due"
5 If not then
6 If today's date is greater than the due date - 7 days AND less than the due date + 1, then "due" (i.e. within 1 week after the due date)
else the last possible value is it's "overdue" (i.e. if it's more than a week after the due date)

 

[scottylad2]

With a mention when the day is due also

Excel Workbook
	A	B
1	01/05/2010
2	02/05/2010
3
4	04/05/2010
5	05/05/2010
6	06/05/2010
7
8	08/05/2010
9	09/05/2010
10	10/05/2010
11	11/05/2010	coming due
12	12/05/2010	coming due
13	13/05/2010	coming due
14	14/05/2010	coming due
15	15/05/2010	coming due
16	16/05/2010	coming due
17	17/05/2010	coming due
18	18/05/2010	Due Today
19	19/05/2010	past Due
20	20/05/2010	past Due
21	21/05/2010	past Due
Sheet1

 

[Tashat]

Thank you JackDanIce and ScottyLad - your help is much appreciated. I can normally work these things out, but for some reason this got the better of me - and it's actually very basic stuff, which is frustrating. I guess I'm quite rusty. Thanks again. Natasha

 

[excelnerdwannabe]

Thread is old but still relevant!

I've taken the formula here and have tried to add a condition to show whether or not an invoice is current:

=IF(E2<TODAY(),"past Due",IF(E2>=TODAY()-7,"coming due",IF(E2>TODAY(),"current")))

The "current" section isn't working - where am I going wrong?

Thank you very much!

 

[dreid1011]

Thread is old but still relevant!

I've taken the formula here and have tried to add a condition to show whether or not an invoice is current:

=IF(E2<today(),"past Due?,IF(E2="">=TODAY()-7,"coming due",IF(E2>TODAY(),"current")))

The "current" section isn't working - where am I going wrong?

Thank you very much!

You seem to be missing the False portion of the second IF statement.

IF( logic, True, IF( logic, True , ... missing false outcome ... )))</today(),"past>

 

[excelnerdwannabe]

Roger that. Thanks.

Here's where I landed:

=IF(A25=TODAY()+7,"coming due",IF(A25>=TODAY(),"current",IF(A25<TODAY(),"past due","-")))

It's working like I want except for the first logical test. Only invoices with due dates exactly 7 days from today are showing up as "coming due." Invoices with due dates 1 to 6 days in the future are showing as "current."

Is it possible to add a parameter to the first argument that will show all invoices due 1-7 days from now as "coming due,", or do I need to do a separate if statement for each additional day 1 through 6?