Conditional Formatting & OR Function

**MatthewIJClark** · 05-08-2017, 05:19 AM

Hi All,

Wondering if anyone can help.

I need to create a timesheet for a member of my staff.

I've been able to use conditional formatting to enter the start and finish times on the spreadsheet is a "1" is entered in any columns M to P.

However, the total hours doesn't recognise the times entered via the conditional formatting for because the inlay number is zero.

So trying to create a formula using IF and OR but having little success, I've attached a copy my spreadsheet.

Any help will be greatly appreciated.

Thanks

**AliGW** · 05-08-2017, 05:22 AM

The syntax of the formula is wrong. In WORDS, please explain what you are trying to get it to do.

**MatthewIJClark** · 05-08-2017, 05:35 AM

Hi AliGW,

Many thanks for your reply.

So what I'm trying to do that if my member of staff works their normal hours they can keys those in normally and the total hours in column "I" does the cumulative total.

But if the member of staff falls in the criteria in any of columns M to P, the conditional formatting kicks in a enters the start and finish times but also need the total hours in column "I" to work with the conditional formatting they don't, so thought using a IF and OR string of formulas might.

For example:

Monday - Start at 08:15 and finish at 12:45 - this is entered manually by the member of staff and columns "I" and "K" formulas work out total hours and difference.

Tuesday - Member of staff on Annual Leave - Puts a "1" in column "M", the conditional formatting kicks in, enters the start and finish time, then the formula which is the other part of the OR formula kicks in because a "1" have been entered and enters the total hours, so the formula in column "k" kicks in.

Originally, this was the formula I was working towards in column "I":

=IF(OR(SUM(E5-D5)+(G5-F5),(IF(SUM(M5:P5)>0,"05:00","0:00")

I hope that makes sense.