Compare Date and Time

Nepheal

New Member
Joined
Jan 27, 2015
Messages
4
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Where does this data come from? Oddly some of the cells are actually true dates. Most aren't but some are. C66 is a true date for example.
 
Upvote 0
Our client provide us this data. Really don't have any idea how they compile it. Can you help me with some example of comparing date in such format using IF statement?
 
Upvote 0
You would be much better to go back to the client and explain the difficulties with the data. However i have written this horrendous looking formula that takes care of the data you have provided in dropbox. If you paste this in L2 and copy it down and across it will convert your text dates to actual dates. There may, and most probably will, be a better formula but this one seems to work fast enough.

=IF(ISTEXT(B2),DATE(MID(B2,FIND("@",SUBSTITUTE(B2,"/","@",2))+1,4),LEFT(B2,FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,FIND("@",SUBSTITUTE(B2,"/","@",2))-FIND("/",B2)-1))+IF(RIGHT(B2,2)="AM",SUBSTITUTE(MID(B2,FIND("@",SUBSTITUTE(B2,"/","@",2))+6,99),"AM","")+0,SUBSTITUTE(MID(B2,FIND("@",SUBSTITUTE(B2,"/","@",2))+6,99),"PM","")+0.5),B2)
 
Last edited:
Upvote 0
Hey Steve,

Looks like you have put lots of brain and time in building up this formula. Incredible! It really worked and made my work very easy. I highly appreciate your unconditional help :)

Cheers,
Dj Jassi
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top