How JustAnswer Works:
  • Ask an Expert
    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
Zabo04, Computer Enthusiast
Category: Microsoft
Satisfied Customers: 283
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

Resolved Question:

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?
Submitted: 1 year ago.
Category: Microsoft
Expert:  Zabo04 replied 1 year ago.
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?
Customer: replied 1 year ago.

Hello,

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)

Allan

Expert:  Zabo04 replied 1 year ago.
I downloaded the attachment and I'm reading through your description.
Expert:  Zabo04 replied 1 year ago.
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.
Expert:  Zabo04 replied 1 year ago.
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?
Expert:  Zabo04 replied 1 year ago.
So it is the index value that is returned, not just one, but any number other than 0 is true (everything is true unless it is false).
Expert:  Zabo04 replied 1 year ago.
Take a look at this and let me know what you think. Obviously 8/3 and 8/6 are not holidays, I just put them in for testing purposes.
Expert:  Zabo04 replied 1 year ago.
Let me know if you want something changed or have trouble downloading the attachment.
Customer: replied 1 year ago.

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?

Expert:  Zabo04 replied 1 year ago.
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.
Expert:  Zabo04 replied 1 year ago.
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.
Expert:  Zabo04 replied 1 year ago.
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.
Customer: replied 1 year ago.

okay with the additional columns that works. Out of interest how are you getting the yellow highlight to come up?

I'll post another question (another new questions etc)

Expert:  Zabo04 replied 1 year ago.
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.
Expert:  Zabo04 replied 1 year ago.
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.
Expert:  Zabo04 replied 1 year ago.
What version of excel are you using?
Zabo04 and 2 other Microsoft Specialists are ready to help you

Related Microsoft Questions