Log In
Register
Home
October 27, 2018
Offline
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
July 16, 2010
Offline
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
October 27, 2018
Offline
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.
October 27, 2018
Offline
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.
October 27, 2018
Offline
Hi Mynda,
i ddecided to insert our entire times.
October 27, 2018
Offline
I attached another Sheet,
i highlighted the ones i wanted it to be 7.5 hours.
July 16, 2010
Offline
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
October 27, 2018
Offline
Hi Mynda,
Thank you once again for your kind and generous help.
October 27, 2018
Offline
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.
July 16, 2010
Offline
Hi Charlie,
Please attach your file containing the formulas. This file is all values.
Thanks,
Mynda
VIP
Trusted Members
December 7, 2016
Offline
Hi Charlie,
Please check attached file to see if this is what you want.
Br,
Anders
October 27, 2018
Offline
Thank you very much Mynda & Anders for the explanation it is rely outstanding, my problem is now totally solved.
Thank you and God Bless
VIP
Trusted Members
December 7, 2016
Offline
Hi Charlie,
You are welcome. I am glad I could help you with your issue.
Br,
Anders
October 27, 2018
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
October 27, 2018
Offline
Hi Anders,
Thank you for the responding to my post.
I see that you relay understood my points, i am happy with that.
My query again was due to mismatch. The formula yous sent was helping but its not giving me the exact hours as I expected. Therefore i sent two spreadsheet:
1. Clock-In and Clock-Out from the Matrix System
2. Is a spread sheet I created to check if an employee clock-in 7.5 or less.
From the second spread sheet you will notice that it has 5 column that contains the following;
Times In, Hours Late, Hours to Be PAID, Total Hours Required, Net Paid Hours, from the Net Paid Hours (NPH) you will notice that it starts docks(cuts) hours at 8:16 am (note that 8 and 16 will be in separate column, but both come under Time In), hence it will show as;
8:16 - 7.23
8:17 - 7.22
8:18 - 7.20
8:19 - 7.18
....and it will continue all the way to 11:00.
Thus i want a Formula that will provide that exact time as what is the second spread sheet.
For those that Clock-Out 40, 30, 20, 15 any minutes early, before 4:30 they hours has to be Docked(cut), because we don't have an arrangement where they can be paid full hours.
With that, I hope, you are clear with my points and I await your kind response.
VIP
Trusted Members
December 7, 2016
Offline
Hello Charlie,
Thank you for the additional explanation, I think I have grasped the picture now. As I still assume you need to copy and paste this formula to every new report you export from the time stamp system I have used a formula with a lot of nested IF functions. I normally avoid this kind of solution as these formulas are most of the time difficult to read and understand. But with this layout of the file you have presented there are not much other options available.
The .xls file format only support a limited number (7) of nested functions, while the .xlsx file format supports far more (64). For this formula to work you need to save your file as .xlsx.
Copy below formula and paste it in cell G7 in TIMES.xlsx workbook. Then copy it further to desired cells.
=IF(AND(ISBLANK(C7),ISBLANK(D7)),"",
IF(OR(ISBLANK(C7),ISBLANK(D7)),0,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)<TIME(8,0,0),TIME(HOUR(D7),MINUTE(D7),0)>=TIME(16,30,0)),7.5,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)>=TIME(8,0,0),TIME(HOUR(C7),MINUTE(C7),0)<=TIME(8,15,0),TIME(HOUR(D7),MINUTE(D7),0)>=TIME(16,30,0)),(TIME(16,30,0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)>=TIME(8,0,0),TIME(HOUR(C7),MINUTE(C7),0)<=TIME(8,15,0),TIME(HOUR(D7),MINUTE(D7),0)<TIME(16,30,0)),(TIME(HOUR(D7),MINUTE(D7),0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24,
IF(AND(TIME(HOUR(C7),MINUTE(C7),0)>TIME(8,15,0),TIME(HOUR(D7),MINUTE(D7),0)>=TIME(16,30,0)),(TIME(16,30,0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24-0.2,
(TIME(HOUR(D7),MINUTE(D7),0)-TIME(HOUR(C7)+1,MINUTE(C7),0))*24-0.2))))))
Some brief explanation of above formula.
First IF checks if cells C7 and D7 are blank, if true then put in blank.
Second IF checks if cells C7 or D7 are blank, if true then put in zero (0).
Third IF checks if time value in cell C7 is lower than 8 am and if time value in cell D7 is greater or equal to 4.30 pm, if true then put in 7.5.
Fourth IF checks if time value in cell C7 is between 8 and 8.15 am and time value in cell D7 is greater than or equal to 4.30 pm, if true then calculate time using values from C7 and 4.30 pm without time penalty.
Fifth IF checks the same apart from that it now checks if time value in cell D7 is lower than 4.30 pm, if true calculate time using values in cells C7 and D7.
Sixth IF checks if time value in cell C7 is greater than 8.15 am and if time value in cell D7 is greater than or equal to 4.30 pm, if true then calculate time using values from C7 and 4.30 pm with 0.2 time penalty.
If all above is not true, then calculate time using time values from C7 and D7 with 0.2 time penalty.
I do hope this is what you are after. I also found no use of your Hours Saved workbook, as there is no need to lookup and find values from a different workbook with this kind of data.
I would still urge you to try to find a different export option as it would be much easier for you if you can export data in a tabular format as you can import using Power Query and create nice looking dynamic reports and so forth.
Br,
Anders
1 Guest(s)
