Excel Spreadsheet Formula

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
wantabe2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

byundtMechanical 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"),"")

Please advise the logic for other possible values.
0
wantabe2Author 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
Saqib Husain, SyedEngineerCommented:
Try

=TEXT(VLOOKUP(B1,{"h",30;"m",90;"lm",180;"l",365},2,0)+D1,"mmmm")
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

byundtMechanical 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

Your issues matter to us.

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

Start your 7-day free trial
byundtMechanical EngineerCommented:
I should have posted a sample file
DatesQ28267916.xlsx
0
[ fanpages ]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
[ fanpages ]IT Services ConsultantCommented:
D'oh!  Just beaten by seconds.

...if only I hadn't typed so much! :)
0
Saqib Husain, SyedEngineerCommented:
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
[ fanpages ]IT Services ConsultantCommented:
Use of YEAR(D1) may produce an error, ssaqibh.

So may YEAR(D2)...YEAR(D5) :)
0
byundtMechanical EngineerCommented:
He'll open the workbook the Asker posted eventually  :-)
0
Saqib Husain, SyedEngineerCommented:
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
wantabe2Author Commented:
i used your code & it works exactly as needed & described. thanks
0
Saqib Husain, SyedEngineerCommented:
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
[ fanpages ]IT Services ConsultantCommented:
i used your code & it works exactly as needed & described. thanks

Did you consider anything I added?
0
[ fanpages ]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
[ fanpages ]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
byundtMechanical 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"),"")

Brad
0
Saqib Husain, SyedEngineerCommented:
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
[ fanpages ]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
byundtMechanical 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.

Brad
0
[ fanpages ]IT Services ConsultantCommented:
Like I said, thanks for your thoughts (both of you), but no need to progress with re-opening the question just for me.

There are plenty of other questions (& other question askers).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.