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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
Here is another way to round down to the minute.

=FLOOR(S1,"00:01")=S2
 
Upvote 0
Thanks for the reply's! Both solutions actually do exactly that I'm looking for, so thank you very much! :)
 
Upvote 0
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".

Attaching screen shot for reference. Please help.
UzSNfwv.png
 
Upvote 0
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")
 
Upvote 0
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!


 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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