Autofill based on 2 other cells

[caj1980]

Trying to build a sheet to assist in calculating available working hours vs required hours for a job. Please help with a formula that will calculate Columns D and E based on the logic below:

Sheet is setup like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Day
[/TD]
[TD="align: center"]Status
[/TD]
[TD="align: center"]STD HRS
[/TD]
[TD="align: center"]OT HRS
[/TD]
[TD="align: center"]TOTAL HRS
[/TD]
[/TR]
[TR]
[TD="align: center"]2-Jul
[/TD]
[TD="align: center"]WEEKDAY
[/TD]
[TD="align: center"]BLACK
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

Column A (DATE)
Column B (Day)= WEEKDAY, WEEKEND, or HOLIDAY
Column C (Status)= BLACK, GRAY, ADVISORY, AWARENESS, or WHITE
Column F (TOTAL HRS)= Sum of D and E

Column D (STD HRS)
If B=WEEKEND or HOLIDAY then D=0; AND if C=BLACK then D=0 regardless of B

Column E (OT HRS)
If B=WEEKEND or HOLIDAY then E=4; AND if C=BLACK then E=0 regardless of B

Same Logic stated differently:
No work can be performed on a BLACK Day
A non-BLACK weekday can have 8 STD HRS and 4 OT HRS
A non-BLACK weekend or HOLIDAY can have 0 STD HRS and 12 OT HRS

Love this forum, always get the help I need! Thanks in advance!

 

[Joe4]

I think you have a typo in your original description (no mention of 8 in column D and E descriptions), but I think this may be what you are after.

Formula for cell D2:
=IF(OR(B2="WEEKEND",B2="HOLIDAY",C2="BLACK"),0,8)

Formula for cell E2:
=IF(C2="BLACK",0,IF(OR(B2="WEEKEND",B2="HOLIDAY"),12,4))

 

[caj1980]

I think you have a typo in your original description (no mention of 8 in column D and E descriptions), but I think this may be what you are after.

Formula for cell D2:
=IF(OR(B2="WEEKEND",B2="HOLIDAY",C2="BLACK"),0,8)

Formula for cell E2:
=IF(C2="BLACK",0,IF(OR(B2="WEEKEND",B2="HOLIDAY"),12,4))

Works perfectly, Joe4, THANK YOU!!

 

[Joe4]

You are welcome.