IF Formula Involving Times

[cgfrank]

Hello
I would like to make it so a cell fills in yes or no based on the content of another cell.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Shift[/TD]
[TD]End Shift[/TD]
[TD]Total Hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]18:00[/TD]
[TD]12:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Log[/TD]
[TD]End Log[/TD]
[TD]Total Hrs on Log[/TD]
[TD]Time Diff Shift vs Log[/TD]
[TD]Violation?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]18:00[/TD]
[TD]12:00[/TD]
[TD]0:00[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]12:00[/TD]
[TD]6:00[/TD]
[TD]6:00[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06:00[/TD]
[TD]21:00[/TD]
[TD]15:00[/TD]
[TD]-3:00[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first table above shows my guys shift time. from 6 am til 6 pm, a total of 12 hours.
The second table shows times recorded on a log. (start time, end time, total hours).
If the value in Time Diff Shift vs Log (which is calculated by subtracting Total Hrs on Log from Total Hrs) is greater than 0:00, fill in Yes.
If its 0:00 or lower, fill in No.
I want the violation column to automatically fill based on the other data

 

[jtakw]

Hi,

Does this help?

Book1
	A	B	C	D	E
1	Start Shift	End Shift	Total Hours
2	6:00	18:00	12:00
3
4
5	Start Log	End Log	Total Hrs on Log	Time Diff Shift vs Log	Violation?
6	6:00	18:00	12:00	0:00	No
7	6:00	12:00	6:00	6:00	Yes
8	6:00	21:00	15:00	-03:00	No
Sheet1

Cell Formulas
Range	Formula
C6	=B6-A6
D6	=IF(C$2-C6<0,"-"&TEXT(ABS(C$2-C6),"hh:mm"),C$2-C6)
E6	=IF(C$2<=C6,"No","Yes")

C6, D6, & E6 formulas copied down

 

[cgfrank]

This is perfect. Thank you!

 

[jtakw]

You're welcome.