Thread: If function with time format
09-15-14, 23:16 #1Registered User
- Join Date
- Sep 2014
Unanswered: If function with time format
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
09-16-14, 04:39 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 ideaI'd rather be riding on the Tiger 800 or the Norton