Automatically allocate priority number based on date & text field

[Talsar]

Hi, this has been driving me daft all day and would really appreciate some help.

As you can see below, I want to have column A automatically allocate a priority number (1, 2, 3, 4 etc), based around 3 formula's;

C Has three values = HIGH, MED, LOW

Where C = HIGH is allocated a higher priority then MED, then LOW

B is deadline dates i was thinking of

If B2>today() & C2="HIGH" Priority = 1
If B2=today() & C2="HIGH" Priority = 2
IF (B2+14)<TODAY() & C2="HIGH" Priority = 3
If B2>today() & C2="MED" Priority = 4
If B2=today() & C2="MED" Priority = 5
IF (B2+14)<TODAY() & C2="MED" Priority = 6
If B2>today() & C2="LOW" Priority = 1
If B2=today() & C2="LOW" Priority = 2
IF (B2+14)<TODAY() & C2="LOW" Priority = 3

A to be automatic
B will always contain a date
C will have one of three values - HIGH, MED, LOW

 

[JackDanIce]

Try this formula in A1 and drag down:

=if(c1="high",if(b1<TODAY(),1,IF(B1<TODAY()+7,2,3)),IF(C1="MED",IF(B1<TODAY(),4,IF(B1<TODAY()+7,5,6)),IF(C1="LOW",IF(B1<TODAY(),7,IF(B1<TODAY()+7,8,9)))))<TODAY(),1,IF(B1<TODAY()+7,2,3)),IF(C1 p ,IF(B1<TODAY(),7,IF(B1<TODAY()+7,8,9)))))< ,IF(B1<TODAY(),4,IF(B1<TODAY()+7,5,6)),IF(C1="LOW" IF(B1<TODAY(),7,IF(B1<TODAY()+7,8,9)))))<TODAY(),1,IF(B1<TODAY()+7,2,3)),IF(C1="MED" , IF(B1<TODAY(),4,IF(B1<TODAY()+7,5,6)),IF(C1="LOW" ="MED">

 

[Talsar]

It would be better to stick with your original thread:

http://www.mrexcel.com/forum/showthread.php?t=469135

where you said your problem was resolved.

It worked on a few dates i had, but it then allocated priority 6 to high level stuff

I realise that i should of wrote better instructions as to what i wanted hence this thread, the older one can be closed as it was a good solution, just not the one i was seeking I could not edit my previous thread to bring it up to date

 

[Talsar]

Try this formula in A1 and drag down:

=if(c1="high",if(b1<today(),1,if(b1><today()+7,2,3)),if(c1="med",if(b1><today(),4,if(b1><today()+7,5,6)),if(c1="low",if(b1><today(),7,if(b1><today()+7,8,9)))))><today(),1,if(b1><today()+7,2,3)),if(c1 p="" ,if(b1=""><today(),7,if(b1><today()+7,8,9)))))>< ,IF(B1<today(),4,if(b1><today()+7,5,6)),if(c1="low" if(b1=""><today(),7,if(b1><today()+7,8,9)))))><today(),1,if(b1><today()+7,2,3)),if(c1="med" ,="" if(b1=""><today(),4,if(b1><today()+7,5,6)),if(c1="low">

Is part of your solution missing?
</today()+7,5,6)),if(c1="low"></today(),4,if(b1></today()+7,2,3)),if(c1="med"></today(),1,if(b1></today()+7,8,9)))))></today(),7,if(b1></today()+7,5,6)),if(c1="low"></today(),4,if(b1></today()+7,8,9)))))></today(),7,if(b1></today()+7,2,3)),if(c1></today(),1,if(b1></today()+7,8,9)))))></today(),7,if(b1></today()+7,5,6)),if(c1="low",if(b1></today(),4,if(b1></today()+7,2,3)),if(c1="med",if(b1></today(),1,if(b1>

 

[Talsar]

Jack thanks for the PM, your soultion works perfectly

All solved now

 

[milesUK]

On behalf of everyone glad you are sorted and thank you for giving us feedback. I certainly appreciate it.

 

[Greg Truby]

Complete formula was:

=IF(C1="HIGH",IF(B1 < TODAY(),1,IF(B1 < TODAY()+7,2,3)),IF(C1="MED",IF(B1 < TODAY(),4,IF(B1 < TODAY()+7,5,6)),IF(C1="LOW",IF(B1 < TODAY(),7,IF(B1 < TODAY()+7,8,9)))))


<TODAY(),1,IF(B1<TODAY()+7,2,3)),IF(C1="MED",IF(B1<TODAY(),4,IF(B1<TODAY()+7,5,6)),IF(C1="LOW",IF(B1< pre TODAY(),7,IF(B1<TODAY()+7,8,9)))))<>
<TODAY(),1,IF(B1<TODAY()+7,2,3)),IF(C1="MED",IF(B1<TODAY(),4,IF(B1<TODAY()+7,5,6)),IF(C1="LOW",IF(B1< p code]< TODAY(),7,IF(B1<TODAY()+7,8,9)))))[>


</PRE>

 

[Joe4]

Moderator Note:

These were two different threads that have now been merged together.
In the future, please post all follow-ups and clarifications back to the original thread (please do not start a new thread).

Thanks

 

[hairyll]

Hai Talsar ,

I'm also new in excel . FYI , i would like to make similar excel file as your . But i do not have an idea how to do it.
If you don't mind can you share the excel file . Really appreciated your help .
air.ryll@gmail.com