Compare Date and Time

[Nepheal]

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!

 

[djjassi13]

I am using US date format.

 

[steve the fish]

And this formula produces a value error?

=E8+0

I cant replicate your error. That should coerce the text value into a number. It does on my machine. I could write a formula but it should be unnecessary.

 

[djjassi13]

=E8+0 gives me #VALUE! (Please refer the screen shot below)

Can you suggest me the idle way of comparing dates? How can I change these dates to type number? and how to get them back to date format? Any help would be appreciable.

 

[steve the fish]

What does this produce in this sheet?

=DATE(2014,12,1)

 

[FormR]

There may be some hidden characters at the beginning or end of the text - what does:

=LEN(E8)

Return?

 

[djjassi13]

=DATE(2014,12,1) is producing "01-12-2014"

 

[djjassi13]

=LEN(E8) is returning 19

 

[FormR]

It's a bit of a mystery then! Can you upload the workbook to a file sharing site like drop box and share the link here. You can remove everything except the problematic dates.

 

[steve the fish]

The date formula is producing a date in UK format so your formulas wouldn't work as excel cant see cell E8 as a date even with coercion. Im not sure if dates can be workbook specific??

 

[FormR]

The date formula is producing a date in UK format so your formulas wouldn't work as excel cant see cell E8 as a date even with coercion.

Well spotted.

@djassi13 - try highlighting column D go to the "Data" tab > Click "Text to Columns" > click "Next" > "Next" > choose "Date" in the Column data format and select MDY from the drop down > Click finish.

Repeat for column E.