I am using excel 2007. I have a column where date and time are present in the format mm/dd/yy hh:mm:ss AM/PM. I need to compare this with timings and based on that need to output string like "NA", "AP", "EU". Sample date/time format is: 8/6/2010 1:31:52 AM

As per the timing given below the above date/time should output as "AP"Condition 08:01:00 AM - 05:00:00 PM NA

05:01:00 PM - 02:00:00 AM AP

02:01:00 AM - 08:00:00 AM EUAny help should be highly appreciated.

First: Your subject line is a bit confusing. It says " Compare date and time" yet your post doesn't deal with thedateat 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 APThe formula in B1 to return AP is:

=VLOOKUP(MID(A1,FIND(".",A1),20)*1,$C$1:$D$4,2,1)

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_lookupargument. 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.

Have Fun!

Hi, Using the setup DerbyDad03 proposed, you could use:

=VLOOKUP(MOD(A1,1),$C$1:$D$4,2,1)

in B1This uses a modulus division by 1 to return the decimal (time) part of the value in A1

Regards

Nice one Humar! That's why you're the Time and Date guru!

Hi All,

Thanks very much for your help on this. VLOOKUP works fine. I used IF check as given below,=IF(AND(((MOD(E2,1))>=TIME(2,0,0)),((MOD(E2,1))<=TIME(8,0,0))),"EUROPE",IF(AND(((MOD(E2,1))>TIME(8,0,1)),((MOD(E2,1))<=TIME(17,0,0))),"NORTH AMERICA","ASIA PACIFIC"))

EU=EUROPE

AP=ASIA PACIFIC

NA=NORTH AMERICA

While your IF statement will certainly work, it's often more efficient to use VLOOKUP, especially if there is anypossibility of changes in the future.With VLOOKUP, you make the changes to the table - once - as opposed to having to make multiple changes within a given formula and then autofilling or copying that formula over a range.

If you reply that changes to the Times and/or Geographic locations will

neverchange then you are required to remember us and promise to come back and apologize when they eventually do. ;-)

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History