If and Contains formulas

[Greenbea]

Working in excel 2010

Looking for a formula which will show if the "Union Notification Date" in cell A2 was received prior to when the work commenced as outlined in "StartDate" cell B2 and "Start Time" cell C2.

The closest I can come up with is using: =IF(ISNUMBER(SEARCH(("*B2*"),A2)),"On Time","Late")

I think the logic is there, but perhaps the date & time formulas are causing problems.

	A	B	C	D
1	Union Notification Date	Start Date	Start Time	Late/On Time
2	10/22/2013 6:26:24 AM	10/22/2013	7:00

<TBODY>
</TBODY>

 

[NBVC]

Maybe?

=IF(B2+C2<=A2,"On Time","Late")

 

[Greenbea]

Thanks Board Regular! The formula is accepted without errors, however it is telling me all of the records (3500+) are On Time....

 

[NBVC]

Are the dates and time columns really date entries or are they text strings that look like dates/times? For me copying your sample to my workbook, I got "Late".

 

[Greenbea]

they're text strings... we broke them out using =IF(B6=3,LEFT(A6,10),LEFT(A6,9)), then converted them to a date value, then checked if they were occuring on the same day =IF(G6<D6,"Late","CheckSameDay") and now just running a time calc to see if the time is occuring later.

Kind of the long way of doing things, and not too clean... but I can't seem to see another way around it.

 

[NBVC]

The LEFT() function returns text.. try coercing the results...

=IF(B6=3,LEFT(A6,10),LEFT(A6,9))+0

then format as date or time as desired.