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.
[TABLE="width: 528"]
<tbody>[TR]
[TD]Priority[/TD]
[TD]SN / Machine / Customer[/TD]
[TD]Importance[/TD]
[TD]Effort[/TD]
[TD]Urgency[/TD]
[TD]Due[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3/25/2015[/TD]
[/TR]
</tbody>[/TABLE]


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))))
\