+ Post New Thread
Results 1 to 7 of 7
Coding Thread, MS Excel Formula in Coding and Web Development; Hi, I am creating a Staff Sign in sheet in Excel 2007. I have a monthly sheet on each page ...
  1. #1

    Join Date
    Feb 2012
    Location
    Preston
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    MS Excel Formula

    Hi,

    I am creating a Staff Sign in sheet in Excel 2007. I have a monthly sheet on each page of the workbook. The first few rows consist of the individual dates. I am currently entering the time each staff member arrives by using
    Code:
    (CTRL + SHIFT + ;)
    which inserts the exact time in each appropriate cell. What I am stuck with is I would like a percentage at the end of each week or month amount of lates. For e.g. if the start time is 07:00 and a staff member arrives at 07:02 I would like the spreadsheet to calculate it as a late.

    Is this possible?

    Thanks in advance.
    Last edited by plexer; 22nd September 2013 at 06:48 PM.

  2. #2

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    If the staff member has some sort of id on the sheet then using a lookup table with members and their respective start times should do what you want.

    If you post a sample file I am sure some kind soul will take a closer look

  3. #3

    Join Date
    Feb 2012
    Location
    Preston
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi,

    I have posted a sample file, I would like the file to recognise staff signing in after 08:30 to be considered late. A percentage of the weekly lates will also be of great help.
    Attached Files Attached Files

  4. #4
    steve's Avatar
    Join Date
    Oct 2005
    Location
    West Yorkshire
    Posts
    1,043
    Thank Post
    22
    Thanked 177 Times in 123 Posts
    Rep Power
    52
    I'd use conditional formatting to highlight lates, add a column for the start time and use the following to calculate the percentage lates:

    Code:
    =COUNTIF(E7:I7,">"&C7)/COUNT(E7:I7)*100
    I've put this in the file, but the fancy month choice bit in the top corner breaks it.

    Staff Sign In Sheet.xls

  5. #5

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,209
    Thank Post
    1,809
    Thanked 2,223 Times in 1,641 Posts
    Rep Power
    803
    This sounds like a little job for Google forms which can timestamp the entry and put the results in a spreadsheet.

  6. #6

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    11,225
    Thank Post
    874
    Thanked 2,717 Times in 2,302 Posts
    Blog Entries
    11
    Rep Power
    780
    Quote Originally Posted by elsiegee40 View Post
    This sounds like a little job for Google forms which can timestamp the entry and put the results in a spreadsheet.
    Or Access, or InfoPath, or SharePoint/o365 2013 excel data collection forms

  7. #7

    plexer's Avatar
    Join Date
    Dec 2005
    Location
    Norfolk
    Posts
    13,713
    Thank Post
    667
    Thanked 1,636 Times in 1,462 Posts
    Rep Power
    424
    Are you including sat/sun or not?

    Conditional formatting should do the highlighting although I've put a formula in if minute(cell_ref)>30 but it doesn't work with the ctrl shift ; keypress for some reason only if you type it in.

    Ben

SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 06:18 AM
  2. office for mac excel formula question.
    By russdev in forum Office Software
    Replies: 2
    Last Post: 9th May 2008, 11:51 AM
  3. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM
  4. RANDBETWEEN in MS Excel
    By woody in forum Educational Software
    Replies: 3
    Last Post: 14th February 2006, 01:34 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •