Date & Time checking formula

[jamshoot]

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

 

[njimack]

Excel Workbook
	A	B	C	D
1	Date	Start Time	End Time	Status
2	01-Mar	8:30 AM	9:00 AM	OK
3	01-Mar	10:00 AM	1:05 PM	OK
4	02-Mar	2:10 PM	3:45 AM	OK
5	03-Mar	4:00 PM	6:30 PM	OK
6	03-Mar	5:15 PM	6:00 PM	Conflict
7	03-Mar	3:50 PM	5:00 PM	Conflict
Sheet1

 

[jamshoot]

Excel Workbook
	A	B	C	D
1	Date	Start Time	End Time	Status
2	01-Mar	8:30 AM	9:00 AM	OK
3	01-Mar	10:00 AM	1:05 PM	OK
4	02-Mar	2:10 PM	3:45 AM	OK
5	03-Mar	4:00 PM	6:30 PM	OK
6	03-Mar	5:15 PM	6:00 PM	Conflict
7	03-Mar	3:50 PM	5:00 PM	Conflict
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]

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
	A	B	C	D	E	F
1
2
3	Date	Start Time	End Time	Status		njimack
4	01-Mar	8:30 AM	9:00 AM	OK		OK
5	01-Mar	10:00 AM	1:05 PM	OK		OK
6	02-Mar	2:10 PM	3:45 PM	OK		OK
7	03-Mar	5:45 PM	6:30 PM	OK		OK
8	03-Mar	5:15 PM	6:00 PM	Conflict		Conflict
9	03-Mar	3:50 PM	5:00 PM	OK		Conflict
Sheet6

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

 

[jamshoot]

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]

Modify the operators from >= & <= to > & < per your preference - remembering to reset the Array once you're finished editing of course.

 

[jamshoot]

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