Excel Used as a Time Sheet

[emanna]

Hopefully I can explain this well enough that someone can understand what I'm
asking.

I currently use an Excel spreadsheet to track daily attendance of employees.
This sheet is filled out by department leaders on a daily basis.

I have a column for time in, time out, and a leave code column (this column
is used to show the reason an employee is not at work).

Whether an employee works or not, these columns will be filled out (if
worked the time in/time out will be filled out and if not worked the leave
code column will be filled out).

I need to know how I could make the sheet show an error message if all these
cells are left blank by mistake (i.e. an employee doesn't work today,
therefore the time in/time out cell is left blank but their supervisor fails
to put in a leave code. I would like the error message to alert them to the
fact that the information in these cells are mandatory and that they have
left them blank).

Is this possible?

If I'm not making any sense, please let me know so I can clarify any
confusion.

Thanks in advance.

 

[Dave O]

The easiest way would be to apply Conditional Formatting to a cell so
it turns up red if it is blank. You could work a date calculation in
so the cell turns up red if it is blank and the date is before
tomorrow's date (so only today's entry and previous day's entries would
turn up red, as opposed to future dates).

 

[Dave O]

As an example: Suppose you have dates in column B, starting in B2.
Suppose you require an entry in cell C2. With the cell pointer on C2,
click >Format >Conditional Formatting. Set Condition 1 to "Formula Is"
in the dropdown, and paste this formula into the formula box:
=AND(C2="",B2<TODAY()+1)
.... and set the formatting to color the cell red when conditions are
met.

The cell turns red when C2 is blank and B2, the date cell for that row,
is less than tomorrow's date. I always have problems getting
conditional formatting to work right the first time: if you use this
formula and don't get expected results, click >Format >Conditional
Formatting and make sure Excel didn't add extra " (double quotes) to
the formula: if it did, remove any extras and it should work.

 

[Dave O]

That that I read your post more closely: assume dates start in B2. C2
holds the start time for that date. D2 holds the end time. E2 holds
the leave code. The conditional formatting for C2 is
=AND(C2="",E2="",B2<TODAY()+1)
This turns the cell red if C2 is blank and E2 is blank.

Conditional formatting for D2 is
=AND(D2="",E2="",B2<TODAY()+1)
D2 turns red if C2 and E2 are blank.

Conditional formatting for E2 is
=AND(E2="",AND(C2="",D2=""),B2<TODAY()+1)
E2 is red if both C2 and D2 are blank, and E2 is blank.

If an entry is made to C2 or D2, no entry will be expected in E2 so E2
flips from red to normal.

 

[emanna]

Thanks Dave for the information that you have already given me, however, I
still remain confused (which believe me, is not you, it's me...lol).

I tried what you told me and of course changed the cells to accomodate my
spreadsheet. Only certain cells then would highlight red.

So, I was hoping that if I mentioned what cells were what, then it may help
in trying to tell me what I need to do.

A11:A23, A26:A34, A37:A40, A42:A45 are the time in cells

D1123, D2634, D3740, D4245 are the time out cells

E11:E23, E26:E34, E37:E40, E42:E45 are the leave code cells

F8 is the date cell which is programmed to automatically put in today's date
upon opening the sheet.

Again, I would need a message/cells to highlight if there was nothing
entered in the time in/time out and leave code cells.

Thanks in advance

 

[HoseHead78]

Microsoft offers templates like these..

Check out...
http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=Time+Sheets

Or search for one here
http://office.microsoft.com/en-us/templates/default.aspx

 

[Dave O]

Hi, Emmana-
Your conditional formatting formula in A11 is
=AND(A11="",E11="")
Note that there is no consideration of a date, since from your
description it sounds like each sheet represents a single day.

Your conditional formatting formula in D11 is
=AND(D11="",E11="")

Your conditional formatting formula in E11 is
=AND(E11="",AND(A11="",D11=""))

You can copy those cells into the rest of your range.