• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Calculating Hours for payroll purposes |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Calculating Hours for payroll purposes |General Excel Questions & Answers|Excel Forum|My Online Training Hub

office scripts course

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Calculating Hours for payroll purpo…
sp_PrintTopic sp_TopicIcon
Calculating Hours for payroll purposes
Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
1
November 6, 2018 - 7:42 am
sp_Permalink sp_Print sp_EditHistory

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  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 6, 2018 - 8:27 am
sp_Permalink sp_Print

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

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
3
November 6, 2018 - 8:29 am
sp_Permalink sp_Print

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. 

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
4
November 6, 2018 - 8:30 am
sp_Permalink sp_Print

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.

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
5
November 6, 2018 - 9:43 am
sp_Permalink sp_Print

Hi Mynda,

i ddecided to insert our entire times.

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
6
November 6, 2018 - 3:54 pm
sp_Permalink sp_Print

I attached another Sheet,

i highlighted the ones i wanted it to be 7.5 hours.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
November 6, 2018 - 8:51 pm
sp_Permalink sp_Print

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

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
8
November 7, 2018 - 7:06 am
sp_Permalink sp_Print

Hi Mynda,

 

Thank you once again for your kind and generous help. 

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
9
November 9, 2018 - 1:39 pm
sp_Permalink sp_Print sp_EditHistory

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. 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
November 10, 2018 - 9:19 am
sp_Permalink sp_Print

Hi Charlie,

Please attach your file containing the formulas. This file is all values.

Thanks,

Mynda

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
11
November 11, 2018 - 1:23 am
sp_Permalink sp_Print

Hi Charlie,

Please check attached file to see if this is what you want.

Br,
Anders

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
12
November 12, 2018 - 7:15 am
sp_Permalink sp_Print

Thank you very much Mynda & Anders for the explanation it is rely outstanding, my problem is now totally solved.

 

Thank you and God Bless 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
13
November 12, 2018 - 4:02 pm
sp_Permalink sp_Print

Hi Charlie,

You are welcome. I am glad I could help you with your issue.

Br,

Anders

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
14
November 27, 2018 - 5:47 pm
sp_Permalink sp_Print

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 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
15
November 28, 2018 - 8:31 am
sp_Permalink sp_Print

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

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
16
December 4, 2018 - 8:40 am
sp_Permalink sp_Print

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.  

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
17
December 6, 2018 - 3:39 am
sp_Permalink sp_Print

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

Avatar
Charlie Kama
Papua New Guinea
Member
Members
Level 0
Forum Posts: 20
Member Since:
October 27, 2018
sp_UserOfflineSmall Offline
18
December 7, 2018 - 12:02 pm
sp_Permalink sp_Print

Hi Anders,

 

Thank you very much and appreciate you help.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Alan Sidman, John DiMatteo, Leigh Thomas
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Trevor Pindling
Stevan Kanjo
Erin Sheldon
Nikita Bhatia
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Young You
Jennifer Owens
Forum Stats:
Groups: 3
Forums: 24
Topics: 6525
Posts: 28555

 

Member Stats:
Guest Posters: 49
Members: 32808
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.