Date & Time checking formula

jamshoot

Board Regular
Joined
Oct 15, 2009
Messages
199
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Modify the operators from >= & <= to > & < per your preference - remembering to reset the Array once you're finished editing of course.
 
Upvote 0

Forum statistics

Threads
1,224,552
Messages
6,179,484
Members
452,917
Latest member
MrsMSalt

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