Dear Mynda,
I find out your web site through you tube and i am very happy to be a member.
I have problem with calculating work hours in Excel and please, i'd like your help.
I am an HR Officer employed with Comrade Trustee Service Limited (CTSL) and we normally calculate hours manually in every pay period. Therefore i'd like an effect way to calculate the Net Hours fast using excel formula.This is how our times are implemented:
1. the normal hours required each day is 7.5 excluding lunch hour.
2. Clock in time is 8:00 am but 15 minutes is given as grace period. which means if an employee comes (Clock In) after 8:15 am His/her hour is docked(cut by 0.2 hours)
3. Clock Out time is 4:30 pm (16:30 pm) and hours worked after 4:30 pm (4:31,4:324:33....)is not calculated unless an employee applies for Over Time, (the same applies to morning hours).
Now my Query.
The table below shows the times of one of our officer. you will notice that on 15th October 2018, his clock-in time is 8:47 am and clock-out time is 16:33 pm (4:30 pm). Extra Hours worked is 0.27 hours which will be excluded, total hours worked is 7.77 Hours. the Net Hours is not given, which suppose to be 6.72 hours.
| Date | First IN | Last OUT | Gross Hours |
Total Hours | Extra | Net-Work Hours |
Total Overtime |
Less Hours |
| 004 - BENNY BOIL | ||||||||
| 13/10/2018 | - | (7.50) | 0.00 | |||||
| 14/10/2018 | 8:00 | 16:30 | - | (7.50) | 7.5 | |||
| 15/10/2018 | 08:47 | 16:33 | 07:46 | 7.77 | 0.27 | 6.72 | 00:03 | 00:00 |
Note that First In and Last Out is (Clock In & Out) time after the hour (8) is in minutes (47)
As per the above table i relay would want a function that would captured the Net Hours Column in Bold, automatically and registered as 7.5 hours using Excel Formula, excluding Hours worked before 8:15 am and hours worked after 4:30 pm. for example, if Benny happends to come in at 8:00 am and clock-out at 4:30 pm it will automatically calculate as 7.5 hours, but if he comes in as shown above in the table his times has to read automatically as 6.72 hours in a diminishing sequence.
I await your generous help.
Thank you
Hi Charlie,
Please see file attached.
Note: some of your information doesn't tally with the example. Specifically I can't get 6.72 net work hours on 15/10/2018 because I'm assuming the late penalty is in addition to the time he arrived late. i.e. you dock a further 12 minutes from his time for that day? If this isn't the case then you can just remove this column.
Mynda
Thank you very much Mynda and i appreciate your help very much.
i will try work with the attachment you sent, but please, i will still come back for help if i encounter some complications with excel.
I am also thinking of doing your training online but i haven't confirmed as yet, but i'll keep in touch.
Thank you and God Bless.
Hi Mynda,
Please i need your help as i am stuck with few complications.
Note that i applied the functions you have sent it was good, however for some officers as the example below. On Dorothy clock-in at 8:08 am- which is early (before 8:15 am) and clock-out at 16:36 pm which is after 16:30 pm, and therefore i was expecting her hour in Decimal to be 7.5, because she has worked full 7.5 hours from 8:08 am - 4:36 pm, instead it is giving 7.37.
Please help me, how i can make it become 7.5 hours. i would love to have a function that would give 7.5 hours if staff a staff comes in before 8:15 am and after 4:30/16:30 pm. if a staff comes in after 8:15, 8:16, 8:17 etc...am and before 4:30, 4:29,4:28, 4:27 pm etc... it will automatically doc their time.
Also i am attached our entire time & attendance report below, you might want to have a look.
| 037 - DOROTHY DUWABA | in | out | gross hours | lunch | Less Late Penalty | Less Unauthorized Overtime | Net Hours | Hours in Decimal | |
| Monday | 15/10/2018 | 8:11 | 17:13 | 9:01 | 1:00 | 0:00 | 0:43 | 7:18 | 7.30 |
| Tuesday | 16/10/2018 | 8:08 | 16:36 | 8:28 | 1:00 | 0:00 | 0:06 | 7:22 | 7.37 |
| Wednesday | 17/10/2018 | 7:51 | 16:42 | 8:50 | 1:00 | 0:00 | 0:20 | 7:30 | 7.50 |
| Thursday | 18/10/2018 | 8:05 | 16:34 | 8:28 | 1:00 | 0:00 | 0:04 | 7:24 | 7.40 |
| Friday | 19/10/2018 | 8:05 | 16:45 | 8:39 | 1:00 | 0:00 | 0:15 | 7:24 | 7.41 |
| Saturday | 20/10/2018 | ||||||||
| Sunday | 21/10/2018 | ||||||||
| Monday | 22/10/2018 | 7:50 | 16:48 | 8:58 | 1:00 | 0:00 | 0:28 | 7:30 | 7.50 |
| Tuesday | 23/10/2018 | 7:51 | 16:46 | 8:55 | 1:00 | 0:00 | 0:25 | 7:30 | 7.50 |
| Wednesday | 24/10/2018 | 7:35 | 16:44 | 9:09 | 1:00 | 0:00 | 0:39 | 7:30 | 7.50 |
| Thursday | 25/10/2018 | 7:50 | 16:40 | 8:49 | 1:00 | 0:00 | 0:19 | 7:30 | 7.50 |
| Friday | 26/10/2018 | 8:47 | 16:50 | 8:03 | 1:00 | 0:12 | 0:20 | 6:30 | 6.51 |
Hi Mynda,
i tried to attached the entire time & attendance report sheet but it couldn't upload properly, but please if use the above table it would be okay.
Hi Mynda,
i ddecided to insert our entire times.
I attached another Sheet,
i highlighted the ones i wanted it to be 7.5 hours.
Hi Charlie,
See file attached. Note: time on 23/10/18 does not meet your criteria of arriving before 8:15 and leaving after 16:30.
Also, I inserted a formula to extract the day from column B. You have 15/10 entered twice.
Mynda
Hi Mynda,
Thank you once again for your kind and generous help.
Hi Mynda,
Very sorry for bothering you please forgive me, i just have one query.
I applied the functions you've sent and it worked perfectly and i appreciate it very much,
However, i was expecting the Net hours in Decimal to be 7.5 hours or Less, instead it is going beyond up to 8.3, 8.5 8.7, 9.10, which is beyond 7.5.
Please what could i do so that the Net Hours in Decimal is 7.5 or Less and does not exceeds 7.5 hours.
Please see my attachment and hours highlighted in yellow.
Also note that i copy and paste the sheet with the formula to a new sheet thus it can't show the formula. Because the sheet with the formula is an old version and it can't be uploaded to the web page.
Hi Charlie,
Please attach your file containing the formulas. This file is all values.
Thanks,
Mynda
Hi Charlie,
Please check attached file to see if this is what you want.
Br,
Anders
Thank you very much Mynda & Anders for the explanation it is rely outstanding, my problem is now totally solved.
Thank you and God Bless
Hi Charlie,
You are welcome. I am glad I could help you with your issue.
Br,
Anders
HI Mynda and Anders,
i need help from yours again.
i used the formula but there are still some variations, hence I've attached one of my own sheet that I normally use with the times from the system.
I wanted the forlula to correspond to my excel sheet. Please if both of yous can provide a formula that can calculate the time exactly as on my Excel Sheet.
See attachment
Hello Charlie,
I assume you want some help with TIMES.xls.
I also assume that what we see in TIMES.xls is the report output from your time registration system.
So, with the given assumptions, I believe you then have a scenario where you have to put in the formulas again and again, for each report output. Am I guessing correct? With the format we see the data in TIMES.xls we don't have much other options to play with, unless you can have macro built to handle this for you.
A question: Do you have any other output options? What could help a lot is for example a report output where you have the time stamps for each employee and date. What we need to have is the date, employee id, time in and time out, the rest can be calculated. And of course, such report would need to be in a tabular format.
Any way, let's try to go on with the data you now have. It would help if you can explain more what you need help with. My assumption is that you need help to calculate the Net-Work Hours. The layout of the data differs in this file from the previous, so the formulas given earlier needs to be adjusted. Or we add some helper columns. I don't know what you prefer, so I go along with what is.
In your first post you wrote following conditions:
1. the normal hours required each day is 7.5 excluding lunch hour.
2. Clock in time is 8:00 am but 15 minutes is given as grace period. which means if an employee comes (Clock In) after 8:15 am His/her hour is docked(cut by 0.2 hours)
3. Clock Out time is 4:30 pm (16:30 pm) and hours worked after 4:30 pm (4:31,4:324:33....)is not calculated unless an employee applies for Over Time, (the same applies to morning hours).
What I am missing here is what to do if an employee clocks out at 4 pm, 30 minutes earlier than stated clock out time. Is there a cut in time for such scenario as there is if an employee clocks in after 8.15 am?
But, given the conditions we have for the moment, in cell G7 paste in below formula.
=IF(C7>0,IF(TIME(HOUR(C7),MINUTE(C7),0)<=TIME(8,15,0),7.5,7.3),"")
What it does is to check if Clock In time is greater than 0, if true it then checks if Clock In is less or equal to 8.15 am, if true it gives the value of 7.5 else if false it gives the value of 7.3 (deduction of 0.2 hours). If Clock In is empty it will give a blank cell.
This is a very basic formula with very little conditions, but as I assume you get the correct data already regarding extra hours so I have just focused on the Net-Work Hours column here.
I don't know if this is what you want, but if not, please give more details of what you want to have.
Br,
Anders
