table set up for mail merge

[DAWJr]

Hello,  

Bottom Line Up Front:

I am attempting to set up an Excel sheet to use as the Merge Fields for a mail merge.

Background:

I have over 2300 supervisors at varying levels of experience that need the appropriate level of training. I pull a report from our data bank and save as an Excel sheet.  From this data, i am trying to mine the length of time the supervisor has been in their position - Based on their start date and send the notifications accordingly.

First, I added a column for length of years and used this formula =DAYS(NOW(),F2)/365 and moved the decimal down to 2, e.g., "0.12", to help me see if they are in the ranges i need; e.g., less than 1 year, between first and third year, and then the three and over years of experience. - this formula works fine.

Next, to initiate a mail merge to send mass invitations to the different training levels required by their experience, i added another column as "Subject" and used this formula

 =IF(H2<1,"Priority",IF(H2=1.1>=2.9,"CatchUp",IF(H2>3,"Refresher")))

the "Priority" and "CatchUp" work as i have hoped; however, the 1st Challenge - the "Refresher" does not ( i get no "Refresher" results).  To further this challenge, i later discovered some people are on probation and some are not (because they previously held a supervisory position and did not need to redo it, yet they are in a new supervisory position) - so challenge #2 is to fix the above subject formula to include if there is a date in the Probation column to make this a "Priority" // also, if it does not have a date, to make this a "Catch Up".  #3 once i get these formulas fixed, is it possible to change the formatting to highlight Priority as Red, CatchUp as Orange, and Refresher as light green?

The next phase of the mail merge is to establish a body column - which i have not even attempted yet...  Nevertheless, i am hoping that as the Subject Column is corrected that a NEW column of "Body" will reflect accordingly with:

"Priority = You are identified in HR Smart as a New Supervisor still on probation; Please, see the attachment to register. " for

"CatchUp = You are identified in HR Smart as a Supervisor within your first and second years of experience; Please, see the attachment to register." and finally, for

"Refresher = You are identified in HR Smart as a Seasoned Supervisor; Please, see the attachment to register."

Can you help me fix/establish the formulas Please, so that i can then continue building the Mail Merge portion?

here is a screen shot to help you visualize this challenge:

Thank you in advance for even offering to help me.

v/r,

dave

[#2 · Accepted solution]

@DAWJr 

Re challenge #1:

The =IF(H2<1,"Priority",IF(H2=1.1>=2.9,"CatchUp",IF(H2>3,"Refresher"))) will indeed never come to the "Refresher" stage as the second test H2=1.1>=2.9 will always evaluate as TRUE and thus return "CatchUp". Obviously your intention is to test if the value is between 1.1 and 2.9, but that's not the way to achieve it. You could use IF(AND(H2>=1.1,H2<=2.9),...... but that's unnecessary.

Change the order of the logical_tests in stead.

=IF(H2>=3,"Refresher",IF(H2>=1.1,"CatchUp","Priority"))

[#3]

Oh i see now Riny - THANK YOU ! @Riny_van_Eekelen 

[#4]

@Riny_van_Eekelen   

Thank you Riny your formula for column I :

=IF(H2>=3,"Refresher",IF(H2>=1.1,"CatchUp","Priority"))

Works as I had hoped it to !!!

With this I was able to identify my newest supervisors still on probation in column G; I used this conditional formatting rule =G2>=TODAY() to highlight them red.  As you see I have some source data issues to address.

TODAY’S REQUEST FOR YOUR ASSISTANCE IS:

The next phase of the mail merge preparation is to establish the Status and Priority Classification columns – of which i am stuck... 

This is the formula I am starting with but I don’t know how to “connect” the G2 date condition correctly. =IF(I2=Highest,AND(G2=DATE,“will be the appropriate statement – see below"))

Highest = ”New Supervisor still on probation”

CatchUp = ”Supervisor within your first and second years of experience”

Refresher = ”Seasoned Supervisor with three or more years experience" 

Can you help me fix/establish the formulas Please, so that i can then continue building the Mail Merge portion?

here is are two screen shot to help you visualize this challenge:

Thank you in advance for even offering to help me.

v/r,

dave

[#5]

@DAWJr Please see attached file for some examples using nested IFs of IF and SWITCH. Choose which suits you best.

[#6]

Thank you @Riny_van_Eekelen ... I TRULY appreciate your assistance!!!