How to select time stamp ranges and assign the correct tour designation
Category: [Excel] Demo Available
How to select time stamp ranges and assign the correct tour designation
Requirement:
The table below is represented by 3 tours as seen by the look up table below.
Under the Tour heading, it should give me a result that indicates the time stamp belong to Tour 1, and then when the formula hits a different time stamp, it would then say Tour 2, etc...
The problem is that if the user used a nested if statement it will evaluate the condition and when the condition is true it would not evaluate the rest of the conditions. This is where the user is having trouble trying to find a formula that can evaluate three conditions at the same time. "Note: The user has just typed the different tours manually and without using a formula". The user just wanted to show how the results would look like if excel formula was written logically.
| Time Stamp | Tour |
| 05:46:03 | Tour 1 |
| 05:45:58 | Tour 1 |
| 06:22:34 | Tour 1 |
| 07:24:58 | Tour 2 |
| 07:25:03 | Tour 2 |
| 12:19:50 | Tour 2 |
| 19:35:21 | Tour 3 |
| 19:35:22 | Tour 3 |
| 23:34:01 | Tour 1 |
| 03:23:37 | Tour 1 |
| 03:48:24 | Tour 1 |
| 10:43:38 | Tour 2 |
| 09:51:34 | Tour 2 |
| 12:47:46 | Tour 2 |
| 20:03:39 | Tour 3 |
| 22:30:01 | Tour 3 |
| LOOK UP Table | ||
| Tour | ||
| Tour 2 | 7:00:00 | 14:59:59 |
| Tour 3 | 15:00:00 | 22:59:59 |
| Tour 1 | 23:00:00 | 6:59:59 |
Solution:
Obtained from the OzGrid Help Forum.
Solution provided by AlanSidman.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to convert time from xx minutes to xx seconds into HH:MM:SS |
| How to calculate remaining time to a deadline |
| Insert time/date with click on cell but never update |
| How to create a time series frequency conversion |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.