Using multiple"if" "ands'

Golf tee times.
I have a column "I"  that has day name in it  i.e.  Mon thru Sun.
I have other column "J"that has time in it  0700-1900

 I want to show a return value of  "T1", "T2", "T3", "T4", "T5"
i.e  if ($I14="FRI"  and  $J14>TIME(10,50,0) then  $K14="T1" else "T2")

I need to do this for all seven days.  some days have 3 time conditions and 3 'return values.

if ($I14="SUN"  and  $J14>TIME(10,50,0)<TIME(14,30,0) then  $K14="T3" else "T2")

Above probably does not explain well:
 7 days
5 Time values
T1-T5 represent values.

If a golfer plays before 1050 on a Saturday that would need to show T1
If a golfer plays after 1051 and before 1700 on a Saturday that would need to show T2
If a golfer plays after 1700 on a Saturday that would need to show T3
So I need to determine the "DAY" from column I  and then do the if  for each day.
Trying to use one formula for all seven days.

I have wrestled with this for about a month, I can not get it to work for all seven days.

Any direction is appreciated....
tomrectorAsked:
Who is Participating?
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.

Richard FrancisCommented:
Hi Tom,

Out of curiosity, what do you use T4 and T5 for?

Richard.
0
Glenn RayExcel VBA DeveloperCommented:
^with Richard.  I presume you have T4 and T5 assigned for weekday and/or Sunday timeframes.
0
Glenn RayExcel VBA DeveloperCommented:
You don't need multiple IF statements to accomplish this, but rather a lookup matrix that you can derive the proper T-value from any given day and tee time.  Once you have that, you can use INDEX and MATCH functions to return the correct value in the table.

For example, create a table with the day abbreviations in the left column and all the key time values in the top row.
 example matrixUnfortunately, your example question only provides for us the T-values for Saturday at 10:50 and 17:00, but I've added a few others for this demonstration.  NOTE:  you only need enough columns for all the possible breakpoints in the time, plus your start and end times.  These are currently the values in bold on top with light red backgrounds.  If your other days use the same break points, then none of the other columns would be necessary.

I added this table to "Sheet2".  In "Sheet1" I added a column of days (column I) and tee times (column J).  For this demonstration, I'm showing all tee times from 7:00 to 19:00 in ten-minute increments.

To the right, I've added a column (K) that locates the row where the day appears in the matrix on Sheet2; another column (L) that locates the column where the time falls in the range of time values in the matrix on Sheet2 (this is a sorted lookup; it's looking for the value less than one in the range); and a final column (M) that uses the INDEX function to locate the value at the intersection of the row and column values found in K and L.

For example: If the day is SAT and the tee time is 11:00, then the day is on row 7, the time closest match is in column 4 and the result is "T2":
example lookup
All of these formulas can be combined into one (seen in column N):
=INDEX(Sheet2!$A$1:$J$8,MATCH(I2,Sheet2!$A$2:$A$8,0)+1,MATCH(J2,Sheet2!$B$1:$J$1,1)+1)
eliminating the need for the previous three.  But I am showing those also for reference.

Example file attached.
-Glenn
EE-TValues.xlsx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Glenn RayExcel VBA DeveloperCommented:
PS  A variation of the above formula that is a little simpler:
=INDEX(Sheet2!$B$2:$J$8,MATCH(I2,Sheet2!$A$2:$A$8,0),MATCH(J2,Sheet2!$B$1:$J$1,1))

(Changes the INDEX array and removes the +1 added to the row and column arguments.)
0
Glenn RayExcel VBA DeveloperCommented:
Did you have any questions about the possible solutions I offered?  Let us know and I'm sure we can help resolve it.

Again, information on the T4 and T5 categories will be helpful.

Regards,
-Glenn
0
tomrectorAuthor Commented:
Have not been able to get your soultion  to work...
Not sure lookup table will work....

Actual time are by minute.  0701..0702,,0703 ect.

The intent is to have "any time listed" (in Col "J" as described above)  on Saturday (Col "I")  between 0700  and 1050 to show up as T1,
anytime between  1051 and  1400  show as T2  
and any time on Sat between 1401 at 1700  to show as T3.
and any time after 1701 as T4
I tried to attach the actual chart.
tomchart.jpg
0
Glenn RayExcel VBA DeveloperCommented:
Your example table is just a transposed version of the one I created.  It's easy enough to revise the formulas to refer to your table instead and still produce correct results.

I used your table and added "extreme" times to represent boundaries between "sun up" and "sun down" times (in other words, times before or after any possible tee time).  I then changed the formulas to pick up the correct T-values.

Additionally, I have listed all days and "Holiday" and minute-by-minute listings around each Day Part (DP) so that you can see how the formulas work and that they are, in fact, returning your expected T-Values.

I've also added a new set of functions in the Day column on Sheet1 that will return the day of the week or "Holiday" if the date falls on a pre-described holiday (see list in Sheet2).

Regards,
-Glenn
EE-TValues.xlsx
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
Glenn RayExcel VBA DeveloperCommented:
Hi,
Did you have a chance to review/test my latest solution?  I wanted to also point out that columns K, L, & M aren't necessary and can be deleted from the workbook.  The reason they're left in is to help you see the lookup process (using the MATCH function) that derives the correct column and row on the table.

If you have any questions, please let me know.  

Regards,
-Glenn
0
tomrectorAuthor Commented:
The T1- T4  are  all Day Parts,  relates to the fees we charge during the the "Tx" time range.  No one day has T1 thru T5,  i.e  Mondays are T4-T5  while Saturdays have T1-T2-T3.
So I ignored the least populated "Ts"  on any given day with more than 2, since I could not get the IF/AND/OR to work  for me.
0
Glenn RayExcel VBA DeveloperCommented:
Tom,

My solution follows your described rules above.  If you create a PivotTable on the sample data sheet in my example workbook, you'll see that all those rules are applied:
T-values by day
Did you mean to close this question?

-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
My second solution (40349411) used the Asker's own rules table (post 40348917, attached image) in a revised workbook that does an indexed lookup to derive the correct T-Value for any given day of the week and time, including holidays.   I believe it is a complete and workable solution and should be accepted.

Thanks,
-Glenn
0
Richard FrancisCommented:
I agree with Glenn. He's put a lot of work into this question and provided a thorough solution.
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
Web Development Software

From novice to tech pro — start learning today.

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.