# How to use multiple criteria when using IF, 360DAYS & today() functions in one formula???

#### tori14

##### New Member
Hi all, first time poster - just wondering if anyone is able to help me correct the below formula please?

What I need is a formula that will a) calculate the number of days overdue a task is from today, b) if a task is due within 60 days from today, display the message "Due", and c) if a task is due in more than 60 days from today, display the message "Not Past Due".

I've got some of it & it half works...

=IF(DAYS360(J32, TODAY())<61, "Not Past Due", DAYS360(J32, TODAY()))

The reason it half works is that is diplays "Not Past Due" when even when a value is not due more than 60 days from today.

The issue I'm having is that when I try to introduce a second IF function to display the message "Due" if the due date is within 60 days of today, the formula doesn't work. I think the problem is something to do with the fact the numbers generated are negative (because they are not yet overdue).

Can anyone figure out how to do this?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I'm not sure that I completely understand your question, but try this:

=IF(DAYS360(J32,TODAY())<61,"Due","Not past due")

I've also got this, which is a bit closer (I think!):

=IF(DAYS360(J3,TODAY())<-61,"Not Past Due",IF(DAYS360(J3,TODAY())>-61,"Due",DAYS360(J3,TODAY())))

The issue is it doesn't give a numerical value of days overdue in the cells that are overdue, it just automatically says "Due".

Just figured that your dates are in the future.

Try this one

=IF(ABS(DAYS360(J32,TODAY()))<61,"Due",ABS(DAYS360(J32,TODAY()))-60)

Just figured that your dates are in the future.

Try this one

=IF(ABS(DAYS360(J32,TODAY()))<61,"Due",ABS(DAYS360(J32,TODAY()))-60)

Thanks Rada1, that does work mostly - is there a way to introduce a third criteria? Some of my dates are in the past and some are in the future, and I also need cells that are due in more than 60 days to say "Not Past Due".

Can you provide a small sample of your data with desired results, it will be easier for me to do everything correctly?

Unless you are specifically using DAYS360 for that reason you should note that it doesn't give an accurate count of days because (as the name suggests) it treats a year as 360 days and each month as 30 days, so from today (28 May 2015) until 1 June 2015 is 4 days but this formula:

=DAYS360(DATE(2015,5,28),DATE(2015,6,1))

....returns 3

for an accurate count of days just subtract one date from the other.

Using that approach perhaps try this formula

=IF(J3-TODAY()>60,"Not past due",IF(J3>=TODAY(),"Due in "&J3-TODAY()&" days","Overdue"))

If J3 is in the past you get "Overdue" - if J3 is today or up to 60 days in the future you get "Due in x days", if J3 is 61 days or more in the future you get "Not past due"

Does that work for you?

Last edited:
Thanks, that's really helpful; Accuracy is important!

I do actually need it to also give a # of days overdue, so I changed what you gave me a little:

=IF(J3-TODAY()>60,"Not past due",IF(J3>=TODAY(),"Due in "&J3-TODAY()&" days","Overdue by "&J3-TODAY()&" days"))

Now the only thing is it is giving a number value to 11 decimal places? ie: "overdue by - 701.93055555555".

Is there any way to have the result as a round number?

Thanks, that's really helpful; Accuracy is important!

I do actually need it to also give a # of days overdue, so I changed what you gave me a little:

=IF(J3-TODAY()>60,"Not past due",IF(J3>=TODAY(),"Due in "&J3-TODAY()&" days","Overdue by "&J3-TODAY()&" days"))

Now the only thing is it is giving a number value to 11 decimal places? ie: "overdue by - 701.93055555555".

Is there any way to have the result as a round number?

Hey I am getting "Overdue by -78 days" in a cell I tested the formula on!

The format is General, is that what your cell is formatted to??

They are all formatted to general. Could it be to do with the fact that the due date in cell J3 is being calculated by a formula?

Replies
0
Views
205
Replies
1
Views
221
Replies
3
Views
198
Replies
9
Views
778
Replies
7
Views
1K

1,196,374
Messages
6,014,900
Members
441,854
Latest member
Amstaff

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back