Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unhappy Unanswered: If function with time format

    Hello,

    I would like to ask for some help about excel if function. I have created a database in excel for attendance log in and log out since i do not know how to use the software for the biometrics we just bought.


    I have 4 different columns on my spreadsheet, punch in, lunch out, lunch in and punch out. I hope to have the total time they worked for the day. Regular working time is 8 hours. We start at 8:00 in the morning, 11:30 will be lunch, 13:00 will be the return from lunch and 17:00 will be end of the day. Most employee punches in before 8 and it records as it is on my excel when i only hope to get the hour from 8 - 11:30 and 13:00 to 17:00. Anything which is between those working times should remain as it is for me to know if they were late or went home early. How do i create a formula for this? Please help help help

    I am a bit slow for this formula so please bare with me

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Personally I wouldn't use a spreadsheet for this, its not a good technique for data storage in any event, but for things like payroll or clocking on where there could be a legal consequence of problems ... not clever. Why?
    ...well its all to easy to inadvertently change a cells formula and not notice that one cell is out. yes you can go part way by protecting cells but even so...


    however having said that here's a way of doing it:-
    there are other ways but for me, break the task down into constituent parts
    >so record clock on / clock off times

    >then in another cell standardise each of those times. by 'standardise' set to the official clock on/off time if they are early to clock on or late to clock off, but use the actual time if they were late to clock on or early to clock off

    >then summarise the hours worked in the morning and afternoon.

    you will need to do some more work to handle where a clock on/clock off time is missing and general error checking. but this shoudl give you an idea
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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