|First: Your subject line is a bit confusing. It says "Compare date and time" yet your post doesn't deal with the date at all. |
Is it safe to assume that we can ignore the date since you only seem to be comparing times?
Second, there seems to be a problem with your time ranges. Your ranges all end "on the hour" i.e. 0 minutes but they all start at 1 minute after the hour.
What should the output be for 05:00:32 PM? or 02:00:45 AM? or 08:00:01 AM
Shouldn't your ranges all end in :59?
OK, assuming my assumptions above are correct, try this setup:
A B C D
1 8/6/2010 1:31:52 AM AP 12:00:00 AM AP
2 2:01:00 AM EU
3 8:01:00 AM NA
4 5:01:00 PM AP
The formula in B1 to return AP is:
How it works:
Excel stores Dates and Times as numbers, with the integer portion representing the Date and the decimal portion as representing the Time.
e.g. 8/6/2010 1:31:52 AM is stored as 40396.0637962963
The MID and FIND functions extract the decimal portion of the date in A1 as a text string.
MID(A1,FIND(".",A1),20) = .0637962963 (as Text)
Multiplying the string by 1 (or adding 0, etc.) will turn it back into a number.
The VLOOKUP will then lookup that "number" in C1:C4 and return the corresponding value from D1:D4. Remember that the Times in C1:C4 are actually stored as numbers, so that is what VLOOKUP will look for.
Notice that the VLOOKUP function uses 1 (TRUE) as the range_lookup argument. Therefore, if it doesn't find an exact match, it will use the closest value that is not greater than the value it is looking for.
Note: Times for AP have to be listed twice in order to deal with the crossover point of midnight.