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

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
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2011
Messages
6,554
Office Version
  1. 365
Platform
  1. Windows
Here is another way to round down to the minute.

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

Nepheal

New Member
Joined
Jan 27, 2015
Messages
4
Thanks for the reply's! Both solutions actually do exactly that I'm looking for, so thank you very much! :)
 

djjassi13

New Member
Joined
Jan 30, 2015
Messages
10

ADVERTISEMENT

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
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 30, 2015
Messages
10

ADVERTISEMENT

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
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 30, 2015
Messages
10
If I click on the cell, I see the same value in formula bar. It must be text. What do I do now?

ZWr0WFP.png
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,601
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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
Top