Compare date and time in excel

September 13, 2010 at 16:06:02
Specs: Windows XP
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 EU

Any help should be highly appreciated.

See More: Compare date and time in excel

Report •

September 13, 2010 at 17:48:00
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.

Have Fun!

Report •

September 14, 2010 at 04:45:24

Using the setup DerbyDad03 proposed, you could use:
in B1

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


Report •

September 14, 2010 at 04:49:24
Nice one Humar!

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

Report •

Related Solutions

September 14, 2010 at 10:52:53
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"))


Report •

September 14, 2010 at 12:29:46
While your IF statement will certainly work, it's often more efficient to use VLOOKUP, especially if there is any possibility 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 never change then you are required to remember us and promise to come back and apologize when they eventually do. ;-)

Report •

Ask Question