• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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
0
wantabe2
Asked:
wantabe2
  • 8
  • 6
  • 5
  • +1
1 Solution
 
byundtCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
byundtCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
 
byundtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now