Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Zabo04 Your Own Question

Zabo04, Computer Enthusiast

Category: Microsoft

Satisfied Customers: 284

Experience: Experienced in Word, Excel, Access, Powerpoint, and Outlook.

58597962

Type Your Microsoft Question Here...

Zabo04 is online now

I am looking to use Excel timesheet rota. So we just

This answer was rated:

★★★★★

I am looking to use Excel for a timesheet rota. So we just have time in and then time out. Nothing complex, BUT the users want to have either cell colour or text colour to denote things such as holiday. I am looking to add up hours worked in a week per person and will need to separate out holiday hours taken. In using IF functions and colours is this possible?

It is possible using conditional logic. Can you make up a mock time sheet. I am not sure how you want to denote hours worked. I do not need any formulas, just make a fake time sheet filled in the way it should look and put comments on calculated fields and select fill colors and tell me the criteria. How you want holiday hours denoted is what I really need to know. Do you list the date and put the hours in the columns to the left then list holiday dates somewhere or is it an 'x' in a column to mark the day as a holiday?

I cant seem to add an attachment. So basically I have across the top I have days, down the left employee names and then under each day hours worked and in this area they currently show hours worked in a colour (or a cell colour) to denote holiday (this is done in Word at the moment so I am trying to move it to Excel - this is a working document ie printed out and shown to staff). So all hours are in the same area, but holiday is just a different colour.

So my view would be if(a1=yellow,a1,maybe a different question)

I am adding a row that you can hide, and lock if necessary, above the weekday, 1 means holiday, based on the holiday dates listed in the sheet "Holidays". So you put the day/date/year of the holiday, and if the day is a holiday 1 appears, otherwise false. The conditional logic formula uses this value to color the weekday and day date yellow if 1, otherwise no change if false.

Right now you have a total hours worked column. I think you still want that, I am adding a holiday hours worked, do you want a column for non-holiday hours?

That then means all hours in that day are holiday whereas I need to split this per employee, ie they aren't all going to be on holiday on the same day, hence I would prefer highlighting the relevant shift but assume this cant be included in an IF function?

It can be, but at that point you might as well do it manually. What you cannot do is to determine a conditional based on the cell color, only use a condition to set cell color.

I can add a column to the left of the "In" column for each day. If you put an "X" there it can highlight that day's shift for that employee and calculate it for holiday hours.

Look at this revision and let me know if that works. Putting an X, capital X, colors the In, Out, and Calc columns then adjusts the calculated columns.

if you click on one of the yellow cells, then click conditional formatting and manage conditional formatting you can see the rules I have made. Click edit on one of the rules. I used three rules. Each refers to cell B7, because I had C7 selected, the rest are relative reference. So there is a rule for B7 to refer to D7 and B7 for E7. When you put in what cells it applies to, excel knows to reference B8 for C8, etc.

When I added the rule I clicked new rule and then the last option, use formulas. The formula I used was "=if(B7="X", true, false)". So if there is an X in B7 then the formula is true, apply the formatting, which in the add rule box I clicked formatting and selected the yellow fill for the cell.