# Compare Date and Time

#### Nepheal

##### New Member
Hello!

I'm needing to include a validation check in my wsheet that starts with comparing date / time from two separate sources for accuracy. This is for a telephony system comparing to scheduling system for accuracy.

One source (S1 we'll call it) exports as (DD/MM/YYYY HH:MM:SS AM/PM) where the exact second is shown (1/27/2015 10:34:23 PM).
The second source (S2, why not) exports the same minus the seconds, but still includes a "00" for filler (1/27/2015 10:34:00 PM).

I have currently over 3000 lines of S1 and about half that as S2. The goal is to compare en mass and if equal, copy row data associated with matching S2 and paste next to the row with the S1 time.

At this point, the additional seconds of S1 don't match that of S2, ever.

Is there possibly a way of validating S1 if S2 is within a given range (say 1 min +/-) or maybe rounding S1 to the nearest minute?

Any guidance would be appreciated!

Thank you!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### steve the fish

##### Well-known Member
Hi. This formula will produce TRUE if the date, hours and minutes are the same regardless of seconds provided that S2 never has seconds.

=INT(S1)+TIME(HOUR(S1),MINUTE(S1),0)=S2

#### FormR

##### MrExcel MVP
Here is another way to round down to the minute.

=FLOOR(S1,"00:01")=S2

#### Nepheal

##### New Member
Thanks for the reply's! Both solutions actually do exactly that I'm looking for, so thank you very much!

#### djjassi13

##### New Member

Hi All,

I am facing a problem while comparing dates in two different Cell. Cell D8 and E8 has dates in format 'mm/dd/yyyy hh:mm AM/PM'. In cell F8, I have applied formula '=IF(D8>=E8,"OK","FAIL"). The date in D8 is greater than E8, but the formula is returning incorrect result i.e. "FAIL".

#### steve the fish

##### Well-known Member
If that's the case then either E8 or both D8 and E8 are text rather than numbers. What does this produce:

=IF(D8+0>=E8+0,"OK","FAIL")

#### djjassi13

##### New Member

Hey Steve,

D8 and E8 are neither text nor numbers, they are custom format 'mm/dd/yyyy hh:mm AM/PM'.

If I apply the formula
=IF(D8+0>=E8+0,"OK","FAIL") then I get the result #VALUE!

#### steve the fish

##### Well-known Member
Hi. A date and time is a number to excel. Formatting makes no difference to the cell value, just its appearance. If you click into the cell what do you see in the formula bar? If it is text it will be exactly the same. If its a number its true value will appear without formatting.

#### djjassi13

##### New Member
If I click on the cell, I see the same value in formula bar. It must be text. What do I do now?

#### steve the fish

##### Well-known Member
Which region are you in? Would you write the date MM/DD/YYYY? I don't understand why +0 wouldn't work unless you would normally write the date DD/MM/YYYY.

Replies
12
Views
758
Replies
3
Views
253
Replies
2
Views
414
Replies
1
Views
323
Replies
3
Views
715

1,140,933
Messages
5,703,239
Members
421,287
Latest member
Ravi bhatia

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back