In the attached spreadsheet I will be inputting the name, score, city, and last contact. I need the fields "Next Contact" to automatically populate an entry based off of what is typed in the score cell. If an h or H is typed in the score field, I need the cell "Next Contact" to be one month greater than what is typed in the "Last Contact" cell. Is this possible?
1.xlsx
LVL 15
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
The following formula returns the next month if score is "h". Otherwise, it returns an empty string (looks like a blank).
=IF(B2="h",TEXT(--(D2 & " 1")+31,"mmmm"),"")

0
Author Commented:
thanks! The possible values are h, l, lm, and m. Below are the possibilities:

If B2 has an "h" as the value then in E2 needs to be 30 days greater than what is in D2
If B2 has an "m" as the value then in E2 needs to be 90 days greater than what is in D2
If B2 has an "lm" as the value then in E2 needs to be 180 days greater than what is in D2
If B2 has an "l" as the value then in E2 needs to be 365 days greater than what is in D2

I hope this makes sense...thanks for your help!
0
EngineerCommented:
Try

=TEXT(VLOOKUP(B1,{"h",30;"m",90;"lm",180;"l",365},2,0)+D1,"mmmm")
0
Mechanical EngineerCommented:
I modified ssaqibh's formula so it would work on text values in D2
=IFERROR(TEXT(VLOOKUP(B2,{"h",31;"m",93;"lm",186;"l",366},2,0)+(D2 & " 1"),"mmmm"),"")

I also added an IFERROR to handle the possibility of a blank cell in B2.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Mechanical EngineerCommented:
I should have posted a sample file
DatesQ28267916.xlsx
0
IT Services ConsultantCommented:
Hi,

Further to the first reply, place this formula in cell [E2]:

=TEXT(--(D2 & " 1")+IF(LEN(TRIM(B2))>0,CHOOSE((FIND(B2,"h |m |lm|l ")-1)/3+1,30,90,365,180),0),"mmmm")

Copy cell [E2] to the Windows Clipboard, & copy down column [E] to the extent of the data; for example, copy [E2] to [E3:E5].

However, adding 30 days to "October" will result in "October".  Adding 31 will give you "November" (that I think you were aiming for):

=TEXT(--(D2 & " 1")+IF(LEN(TRIM(B2))>0,CHOOSE((FIND(B2,"h |m |lm|l ")-1)/3+1,31,90,365,180),0),"mmmm")

Also, as you do not have any specific year within the values in column [D], adding 365 days to "March" (in cell [D4]) will also result in just "March" (without a year increment).

Perhaps the logic should actually be:

If B2 has an "h" as the value then in E2 needs to be 1 month greater than what is in D2
If B2 has an "m" as the value then in E2 needs to be 3 months greater than what is in D2
If B2 has an "lm" as the value then in E2 needs to be 6 months greater than what is in D2
If B2 has an "l" as the value then in E2 needs to be 12 months* greater than what is in D2

* The issue with no year being specified will still exist.

May I suggest that if you did mean 1/3/6/12 months, then this formula is what is required:

=TEXT(DATE(YEAR(NOW()),MONTH("1-"&D2)+IF(LEN(TRIM(B2))>0,CHOOSE((FIND(B2,"h |m |lm|l ")-1)/3+1,1,3,12,6),0),1),"mmmm yyyy")

In the attached workbook, I have changed column [E], & added columns [F] & [G].

Column [E] shows "NEXT CONTACT" based on 30, 90, 180, or 365 days.
Column [F] is based on 31, 90, 180, or 365 days.
Column [G] is based on 1, 3, 6, or 12 months.  This column also assumes the entry in column [D] is for the current (system) year, & displays an appropriate year in the calculated result.

BFN,

fp.
Q-28267916.xlsx
0
IT Services ConsultantCommented:
D'oh!  Just beaten by seconds.

...if only I hadn't typed so much! :)
0
EngineerCommented:
If the criteria is months instead of days then you can have

=TEXT(DATE(YEAR(D1),MONTH(D1)+VLOOKUP(B1,{"h",1;"m",3;"lm",6;"l",12},2,0),DAY(D1)),"mmmm")
0
IT Services ConsultantCommented:
Use of YEAR(D1) may produce an error, ssaqibh.

So may YEAR(D2)...YEAR(D5) :)
0
Mechanical EngineerCommented:
He'll open the workbook the Asker posted eventually  :-)
0
EngineerCommented:
fp, You are right. I was trying this on a self generated sheet and not the real thing and I thought there would be dates there.

Here is my modification

=TEXT(DATE(1,MONTH("1-"&D2)+VLOOKUP(B2,{"h",1;"m",3;"lm",6;"l",12},2,0),1),"mmmm")
0
Author Commented:
i used your code & it works exactly as needed & described. thanks
0
EngineerCommented:
Yes, brad, I was racking my brains on why you wanted to modify my formula when I finally decided to open the real file.
0
IT Services ConsultantCommented:
i used your code & it works exactly as needed & described. thanks

Did you consider anything I added?
0
IT Services ConsultantCommented:
He'll open the workbook the Asker posted eventually  :-)

(I thought the same!) :)

You are right. I was trying this on a self generated sheet and not the real thing and I thought there would be dates there.

I don't think it mattered.  Brad changed 30 to 31, 90 to 93, 180 to 186, & 365 to 366, & the answer was still seen as "correct" ;)
0
IT Services ConsultantCommented:
Oh well, I got a smile out of this...

[ http://www.experts-exchange.com/M_2996935.html ]

"Remember, if someone helps solve your problem you've posted here...give them the points they deserve. We take our own time to help you so please honor that with points."
0
Mechanical EngineerCommented:
fanpages,
I probably should have posted a formula using the Asker's number of days. This one works too:
=IFERROR(TEXT(VLOOKUP(B2,{"h",30;"m",90;"lm",180;"l",365},2,0)+(D2 & " 6"),"mmmm"),"")

0
EngineerCommented:
fp, earlier in the experts exchange help files I remember reading guidelines for awarding points (I cannot seem to find that now). The main concept "I" had collected from there was that "the" correct answer should be graded and in case of multiple correct answers the first one (unless there is a good reason for selecting a subsequent answer). Any comment which improves on the accepted answer could be selected as an assist.

With this I am satisfied with the acceptance. I do not feel comfortable when the asker selects responses from 10 experts just because they had all contributed.
0
IT Services ConsultantCommented:
That's fine.

What I don't like is when those adding to the question are not acknowledged for their input, especially when they offer something that addresses future issues that the asker may not have considered.

No need to dwell on it, though.
0
Mechanical EngineerCommented:
When two or more people post valid solutions at essentially the same time, I am comfortable with a Split.

When several people post valid solutions, but one solution is palpably better than the others, I am comfortable with that person getting most if not all the points.

When the Asker explains why he picked a certain Comment (or Comments) as the solution, there shouldn't be any grounds for complaint.

And if the Asker thinks either of the first two conditions apply, I'd be glad to reopen the question so points may be assigned differently.