Compare two date&time cells

leo1988

New Member
Joined
Jun 19, 2014
Messages
34
Hi all,

Bit embarrassing that I'd need to post this.

I have thousands of date&time cells in two columns below. but the simple formula comparing one as high than the other doesnt work..! (this site won't let me post the full formula today for some reason)
<J11,"LATE","EARLY")<J11,"LATE","EARLY")
target_comp_date</SPAN>
actual_comp_date</SPAN>
15/04/2014 08:26:21</SPAN>
31/10/2013 11:47:56</SPAN>
30/04/2013 08:41:59</SPAN>
03/01/2013 11:23:43</SPAN>
30/04/2013 09:00:13</SPAN>
03/01/2013 11:24:11</SPAN>
15/04/2014 08:23:02</SPAN>
31/10/2013 11:47:10</SPAN>

<TBODY>
</TBODY>
Thanks
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Your post didnt display properly.
Places spaces before/after any carat < > characters
 
Upvote 0
When posting formulas, if a < sign is followed by a letter, put a space between to stop your formula being chopped off.



You haven't said in what way it doesn't work. Do you just have the Early/Late the wrong way round. If not, more explanantion please.

Excel Workbook
JKL
1target_comp_dateactual_comp_date
215/04/14 8:2631/10/13 11:47Early
330/04/13 8:413/01/13 11:23Early
430/04/13 9:003/01/13 11:24Early
515/04/14 8:2331/10/13 11:47Early
Early or Late
 
Upvote 0
Hi guys,

Thanks for the tip on the posting. Never had that problem before.

It works but not accurately. For instance the last one on the table below is wrong, as is a number of others. P.s. I wrote it wrong in my post above as I was writing it out for the 6th time but its right for those in the table.

Thanks
Leo

target_comp_date</SPAN>actual_comp_date</SPAN>
15/04/2014 08:26:21</SPAN>31/10/2013 11:47:56</SPAN>Early</SPAN>
30/04/2013 08:41:59</SPAN>03/01/2013 11:23:43</SPAN>Late</SPAN>
30/04/2013 09:00:13</SPAN>03/01/2013 11:24:11</SPAN>Late</SPAN>
15/04/2014 08:23:02</SPAN>31/10/2013 11:47:10</SPAN>Early</SPAN>
15/04/2014 08:33:00</SPAN>31/10/2013 11:41:14</SPAN>Early</SPAN>
30/04/2013 09:08:06</SPAN>03/01/2013 11:24:46</SPAN>Late</SPAN>
15/04/2014 09:06:04</SPAN>31/10/2013 12:09:15</SPAN>Early</SPAN>
30/04/2013 09:09:52</SPAN>03/01/2013 11:25:02</SPAN>Late</SPAN>
30/04/2013 09:13:16</SPAN>03/01/2013 11:25:17</SPAN>Late</SPAN>
15/04/2014 08:14:07</SPAN>31/10/2013 10:33:41</SPAN>Early</SPAN>
01/04/2014 14:35:59</SPAN>31/10/2013 10:03:13</SPAN>Early</SPAN>
16/04/2014 08:38:05</SPAN>31/10/2013 12:16:42</SPAN>Early</SPAN>
30/04/2013 09:27:35</SPAN>03/01/2013 11:25:40</SPAN>Late</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>
 
Upvote 0
They all look early to me, and that is what the formula is returning for me.
My formula in L2 is copied down.

Excel Workbook
JKL
1target_comp_dateactual_comp_date
215/04/14 8:2631/10/13 11:47Early
330/04/13 8:413/01/13 11:23Early
430/04/13 9:003/01/13 11:24Early
515/04/14 8:2331/10/13 11:47Early
615/04/14 8:3331/10/13 11:41Early
730/04/13 9:083/01/13 11:24Early
815/04/14 9:0631/10/13 12:09Early
930/04/13 9:093/01/13 11:25Early
1030/04/13 9:133/01/13 11:25Early
1115/04/14 8:1431/10/13 10:33Early
121/04/14 14:3531/10/13 10:03Early
1316/04/14 8:3831/10/13 12:16Early
1430/04/13 9:273/01/13 11:25Early
Early or Late
 
Upvote 0
Hi Peter,

Thanks for posting.

I agree the formula is correct and all are early, but even copying and pasting your formula into L2 and copying it down has the first one as Late as well as a number of others.

Any idea what could be causing this? Something wrong with the data itself? :-/
 
Upvote 0
Any idea what could be causing this? Something wrong with the data itself? :-/
Could be. Pick a wrong row. Suppose it is row 3. In vacant cells put these formulas and see what they return

=ISNUMBER(J3)
=ISNUMBER(K3)
=IF(TRIM(K3)-TRIM(J3)<0,"Early","Late")
 
Upvote 0
Both came back as false, the TRIM function works and its good now. Must have been trailing spaces or something! Thanks for the suggestions Peter
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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