Excel Magic Trick 1359: Split Times Values Into 8 Equal Zones: VLOOKUP, LOOKUP or INT/HOUR?

Download Files:

Start File and Finished File:

Learn about the basics of the VLOOKUP Function and about Time Values in Excel. Topics in this video:
1. (00:12) Introduction to goal of video
2. (01:08) Build Lookup Table with Time Values/Time Categories (8 Equal Time Zones, like One, Two, Three…)
3. (01:29) Learn about Excel Time Values
4. (03:32) Time Values and Time Number Formatting. Time Values represent the proportion of a 24 hour day.
5. (05:19) VLOOKUP function to get Text time categories (8 Equal Time Zones, like One, Two, Three…) for 24 hour times. Learn about the basics of how VLOOKUP works.
6. (08:28) How to understand Approximate Match VLOOKUP.
7. (09:32) VLOOKUP function to get time categories (8 Equal Time Zones, like 1, 2, 3…) for 24 hour times
8. (10:17) LOOKUP Function with Array Constant
9. (13:29) HOUR & INT function solution
10. (15:50) INT and *24 solution
11. (16:42) Summary

[ashishbansa]

The best excel channel and most helpful channel on internet.

[zaighamuddinfarooqui]

Treasure of best ever videos on XL. Wonderful Mike!!! :)

[briandennehy]

Mike you are a legend and I hope you realise it! Thank you for these videos

[entertainmentgalaxy]

True Magic... Unbelievable methods... likes

[bannerstamp]

I'm very appreciate the work and dedication that you invest into this videos. Your videos helped me hundreds of times, and I'm constantly learning more from you, for 6 years already. Thank you very much.

[ashishbansa]

wow! thanks a ton for the solution.

Your response was really fast like your excel techniques.

Thanks again!

[lauraeccleston]

could you do a similar thing using days, weeks and months instead of hours and minutes...ps I love your channel!

[manishpandey]

Hi Sir, suppose we have a multiple times and we have to put it under some time slot like less than 1 am, between 1am to 3 AM then 3 AM to 5am then more than 5 am. Could you please explain me how can we do this?

[vmjamshad]

Thanks Mike for the videos. It really helps.

Do you have a video which will help me to create a time-sheet format from a attendance row data. I have multiple shifts timings starting from one day evening and end in next day morning. Please help

[suyashsomani]

Could you please guide which particular excel workbook to use to follow and practice this video, the link re-directs to a page with many files but no specific indication to which one corresponds to this tutorial. Thanks.