Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
emanna
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
emanna
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
HoseHead78
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Time Sheet - Rounding To Quarter Hours C A Excel Worksheet Functions 2 October 18th 05 07:21 PM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
Excel won't copy outside sheet Jack Sons Excel Discussion (Misc queries) 6 May 17th 05 10:05 PM
EXCEL FORMAT PROBLEM WHEN SENDING EXCEL SHEET AS MESSAGE BODY IN . P.S.Sodha Excel Discussion (Misc queries) 0 April 2nd 05 01:53 PM
Calculating tvl time in Excel 2000 Rev.9.2720 Brandi Excel Worksheet Functions 5 January 27th 05 10:15 PM


All times are GMT +1. The time now is 10:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"