Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
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 D11:D23, D26:D34, D37:D40, D42:D45 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 :) "emanna" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
Microsoft offers templates like these.. Check out... http://office.microsoft.com/en-us/re...ry=Time+Sheets Or search for one here http://office.microsoft.com/en-us/te...s/default.aspx -- HoseHead78 ------------------------------------------------------------------------ HoseHead78's Profile: http://www.excelforum.com/member.php...o&userid=32045 View this thread: http://www.excelforum.com/showthread...hreadid=517855 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Used as a Time Sheet
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Time Sheet - Rounding To Quarter Hours | Excel Worksheet Functions | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Excel won't copy outside sheet | Excel Discussion (Misc queries) | |||
EXCEL FORMAT PROBLEM WHEN SENDING EXCEL SHEET AS MESSAGE BODY IN . | Excel Discussion (Misc queries) | |||
Calculating tvl time in Excel 2000 Rev.9.2720 | Excel Worksheet Functions |