Need help in priority list

[Got a problem]

Hi I am working on a priority list and encountered some problems with it. I would really appreciate all the help, thanks.

What I am trying to do is to have excel automatically assign a specific number to the Urgency Column based on how many days the item is from the Due Date.

Priority	SN / Machine / Customer	Importance	Effort	Urgency	Due
6		A	1	1	3/25/2015

<tbody>
</tbody>

So I want the Urgency column show the following number if the following is true
1 - if today is passed the due date
2 - if today is the due date
3 - if the due date is in 1 to 3 days
4 - if the due date is in 4-5 days
5 - if the due date is in more than 5 days.

I have been trying to create formulas but I am not able to set those day restrictions.

 

[gsistek]

Assuming the due date is in F2:
=MATCH(F2-TODAY(),{-1,0,1,4,6})

 

[snrulz]

Assuming the due date is in cell F2
=IF(F2 < TODAY(),1,IF(F2=TODAY(),2,IF(AND(F2-TODAY() > 1,F2-TODAY() < 4),3,IF(AND(F2-TODAY() > 3,F2-TODAY() < 6),4,5))))

 

[Special-K99]

Try
=LOOKUP(F2-TODAY(),{-1E+99,0,1,4,6},{1,2,3,4,5})

where F2 is your date

 

[Zakkaroo]

=MATCH(F2-TODAY(),{-1,0,1,4,6})

I think that will result in an error if the task is more than 1 day overdue?

 

[Special-K99]

Assuming the due date is in cell F2
=IF(F2 < TODAY(),1,IF(F2=TODAY(),2,IF(AND(F2-TODAY() > 1,F2-TODAY() < 4),3,IF(AND(F2-TODAY() > 3,F2-TODAY() < 6),4,5))))

Looks like corruption on the original reply due to < > signs (insert spaces around them), it should be as above

 

[bbott]

Another option:

=IFERROR(LOOKUP(F2-TODAY(),{0,1,3,5},{2,3,4,5}),1)

 

[Got a problem]

Hi all, Thanks for the quick replies!

Snrulz I think you you got it! I jsut did one minor change and it seems to work like I wanted it to work! Thank you for help.

And thank you all for helping, could not have done it without you all.

 

[Got a problem]

=if(f3<today(),1,if(f3=today(),2,if(and(f3-today()>0,f3-today()<4),3,if(and(f3-today()>3,f3-today()<6),4,5))))
\