Date & Time checking formula

jamshoot

Board Regular
Joined
Oct 15, 2009
Messages
198
Hi

I need to do a check on the date & time & display a "OK" or "Conflict" in the Status column. The date & time must not conflict with any of the dates & times.
Can anyone help me with a function ?


<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 80px;"> <col style="width: 80px;"> <col style="width: 80px;"> <col style="width: 80px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center; font-size: 8pt;">Date</td> <td style="text-align: center; font-size: 8pt;">Start Time</td> <td style="text-align: center; font-size: 8pt;">End Time</td> <td style="text-align: center; font-size: 8pt;">Status</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-size: 8pt;">01-Mar</td> <td style="text-align: center; font-size: 8pt;">8:30 AM</td> <td style="text-align: center; font-size: 8pt;">9:00 AM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-size: 8pt;">01-Mar</td> <td style="text-align: center; font-size: 8pt;">10:00 AM</td> <td style="text-align: center; font-size: 8pt;">1:05 PM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-size: 8pt;">02-Mar</td> <td style="text-align: center; font-size: 8pt;">2:10 PM</td> <td style="text-align: center; font-size: 8pt;">3:45 AM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">4:00 PM</td> <td style="text-align: center; font-size: 8pt;">6:30 PM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">5:15 PM</td> <td style="text-align: center; font-size: 8pt;">6:00 PM</td> <td style="text-align: center; background-color: rgb(255, 255, 0); font-size: 8pt;">Conflict</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">3:50 PM</td> <td style="text-align: center; font-size: 8pt;">5:00 PM</td> <td style="text-align: center; background-color: rgb(255, 255, 0); font-size: 8pt;">Conflict</td></tr></tbody></table>
Cheers
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Excel Workbook
ABCD
1DateStart TimeEnd TimeStatus
201-Mar8:30 AM9:00 AMOK
301-Mar10:00 AM1:05 PMOK
402-Mar2:10 PM3:45 AMOK
503-Mar4:00 PM6:30 PMOK
603-Mar5:15 PM6:00 PMConflict
703-Mar3:50 PM5:00 PMConflict
Sheet1
 

jamshoot

Board Regular
Joined
Oct 15, 2009
Messages
198
Excel Workbook
ABCD
1DateStart TimeEnd TimeStatus
201-Mar8:30 AM9:00 AMOK
301-Mar10:00 AM1:05 PMOK
402-Mar2:10 PM3:45 AMOK
503-Mar4:00 PM6:30 PMOK
603-Mar5:15 PM6:00 PMConflict
703-Mar3:50 PM5:00 PMConflict
Sheet1

Hi nijimack

Hi there. Thks for the fast help. I 4get to mention one thing. My date and time are entered incremental, meaning, when I get the data, I will enter it ... no sorting at all.
For example, A5 was enter earlier than A6 & A7 - I want the conflict to reflect on A6 & A7 & not A5. When I use your method to copy the function down, I get conflict in D5 & D6 ....and OK for D7.
Can this be possible.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
jamshoot said:
when I get the data, I will enter it ... no sorting at all.

Given the above, if I've understood, would the below adaptation suffice ?

Excel Workbook
ABCDEF
1
2
3DateStart TimeEnd TimeStatusnjimack
401-Mar8:30 AM9:00 AMOKOK
501-Mar10:00 AM1:05 PMOKOK
602-Mar2:10 PM3:45 PMOKOK
703-Mar5:45 PM6:30 PMOKOK
803-Mar5:15 PM6:00 PMConflictConflict
903-Mar3:50 PM5:00 PMOKConflict
Sheet6


I modified the entry in row 7 slightly to demo. the different outputs of each.
 

jamshoot

Board Regular
Joined
Oct 15, 2009
Messages
198

ADVERTISEMENT

Hi DonkeyOte

Thank u so much. This is something I want. However there is one more thing (sorry) .... how to modify your function so that if the start time is the same as the End time - the status show "OK" & not "Conflict". Right now, the function show "Conflict" in row 11.

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 80px;"> <col style="width: 80px;"> <col style="width: 80px;"> <col style="width: 80px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center; font-size: 8pt;">Date</td> <td style="text-align: center; font-size: 8pt;">Start Time</td> <td style="text-align: center; font-size: 8pt;">End Time</td> <td style="text-align: center; font-size: 8pt;">Status</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-size: 8pt;">01-Mar</td> <td style="text-align: center; font-size: 8pt;">8:30 AM</td> <td style="text-align: center; font-size: 8pt;">9:00 AM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-size: 8pt;">01-Mar</td> <td style="text-align: center; font-size: 8pt;">10:00 AM</td> <td style="text-align: center; font-size: 8pt;">1:05 PM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-size: 8pt;">02-Mar</td> <td style="text-align: center; font-size: 8pt;">2:10 PM</td> <td style="text-align: center; font-size: 8pt;">3:45 AM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">4:00 PM</td> <td style="text-align: center; font-size: 8pt;">6:30 PM</td> <td style="text-align: center; font-size: 8pt;">OK</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">5:15 PM</td> <td style="text-align: center; font-size: 8pt;">6:00 PM</td> <td style="text-align: center; font-size: 8pt;">Conflict</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">3:50 PM</td> <td style="text-align: center; font-size: 8pt;">5:00 PM</td> <td style="text-align: center; font-size: 8pt;">Conflict</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">5:00 PM</td> <td style="text-align: center; font-size: 8pt;">7:00 PM</td> <td style="text-align: center; font-size: 8pt;">Conflict</td></tr> <tr style="height: 22px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="text-align: center; font-size: 8pt;">03-Mar</td> <td style="text-align: center; font-size: 8pt;">7:00 PM</td> <td style="text-align: center; font-size: 8pt;">8:00 PM</td> <td style="text-align: center; background-color: rgb(255, 255, 0); font-size: 8pt;">Conflict</td></tr></tbody></table>
Cheers
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Modify the operators from >= & <= to > & < per your preference - remembering to reset the Array once you're finished editing of course.
 

jamshoot

Board Regular
Joined
Oct 15, 2009
Messages
198
Modify the operators from >= & <= to > & < per your preference - remembering to reset the Array once you're finished editing of course.

Hi DonkeyOte,

thks for your quick reply. It works perfectly.

cheers
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

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