• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Payroll Project|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Payroll Project|General Excel Questions & Answers|Excel Forum|My Online Training Hub
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…Payroll Project
sp_PrintTopic sp_TopicIcon
Payroll Project
Avatar
Joseph Horling
Member
Members
Level 0
Forum Posts: 15
Member Since:
June 24, 2018
sp_UserOfflineSmall Offline
1
October 17, 2019 - 9:23 am
sp_Permalink sp_Print

Hi,

I am creating a payroll project.  On the week ending 11/01/2019 time sheet, I have total regular hours = to 23.50.  On 10/30 I reflected the hours to 5.5 while using 2.5 hours of sick time.  On 10/31 there is a total of 2 hrs with 6 hours of vacation time.  Is there a formula that I can use to adjust those days to equal to 8 hrs?  Also in cells N2 and O2 I have the total hrs of vacation time and sick time, is there a formula that I can show that I used 5.5 hrs of sick time and 2 hrs of vacation time in those cells?  Hope I explained it right.  Thanks for your help....Joe from Michigan.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
October 18, 2019 - 5:40 am
sp_Permalink sp_Print

Hello,

Your attachment seems to be without data.

If you haven’t already, do check out this article, it contains good info. https://www.myonlinetraininghu.....ion-tricks

Avatar
Joseph Horling
Member
Members
Level 0
Forum Posts: 15
Member Since:
June 24, 2018
sp_UserOfflineSmall Offline
3
October 18, 2019 - 11:18 am
sp_Permalink sp_Print

Hi Anders, 

Thanks for replying and my apologies for giving the wrong spreadsheet.  Yes I have looked at the link you provided me.  I also attached the right one if you can take a look at it again.   Thanks again.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
October 19, 2019 - 9:45 pm
sp_Permalink sp_Print

Hello Joseph,

Thanks for the data.

For the ease of working with and maintaining the data I would suggest that you gather the time attendance data in a tabular format. Please see more about tabular format here. https://www.myonlinetraininghu.....ata-format

When the data is in a tabular format you can use whatever design you want to show the data, for example the layout you have in your sample file. Having the data in a tabular format also makes it a lot easier to sum up for example overtime and vacation days.

Anyway, I will try to answer your questions.

Question 1) Is there a formula that I can use to adjust those days to equal to 8 hrs?
I don't fully understand what it is you want here. I assume you want the total hours column to show 8 hours, but I don't see why you would want that. My assumption is that the total hours column is showing total amount of worked hours. But again, as I don't fully understand what it is you want it can be that I make a wrong assumption here.

Question 2) Also in cells N2 and O2 I have the total hrs of vacation time and sick time, is there a formula that I can show that I used 5.5 hrs of sick time and 2 hrs of vacation time in those cells?
If the data had been in a tabular format it would be easy, with the current layout it is also easy enough as we know where to look, but the moment you add more data then you would most likely have to adjust the formula. Based on what I see I assume you want the sick and vacation time to be deducted from the values already existing in those two cells. If so and also assuming that the layout will be the same when adding new data you can use following formulas:

Vacation time: =120-SUM(R:R)
Sick time: =120-SUM(Q:Q)

The formulas above are as simple as it gets, there is no check for date intervals or other conditions.
I hope I have been able to give some help, else just keep posting your questions.

Avatar
Joseph Horling
Member
Members
Level 0
Forum Posts: 15
Member Since:
June 24, 2018
sp_UserOfflineSmall Offline
5
October 20, 2019 - 5:13 am
sp_Permalink sp_Print

 I just want to thank you for the great advice and help. This is just a project I was doing for my excel class.  I wanted to show everything from bonuses to sick and vacation time.  I have some more names that I wanted to add and make a dashboard to summarize each employee to complete my project.  I need to make sure that I have the formulas correct, could you just take a glance and check if I did.  I think I got a little confused when I added the sick and vacation time columns lol.  Thanks so much again.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
October 20, 2019 - 9:00 pm
sp_Permalink sp_Print

Hello Joseph,

If you are new to working with Excel tables then I can only recommend you to participate in this Excel Tables course. It gives you good insight on how to work with data more efficiently. What you have done in your new sample file is just to copy the existing data and layout and pasted it to an Excel table, so you have not really gained anything.

As you are not being specific on what you want help with I have to guess, and spending time on something that later on perhaps turns out to be that I had wrong assumptions on what you need help with is just a waste of my time. Please be more specific on what you want help with.

If you need ideas and examples on how to build such payroll dashboard then do check out the numerous different templates that are available on the Internet. If lucky you find a template that is good enough to use and continue to build on.

Below are some minor advises from me.
- Specify what data you need and why you need it. Ask yourself what it is you want to show with that data.
- If several peoples are to use the file and fill in data, make sure it is clear and simple for them to add the data.
- Separate the data and reports in different sheets.
- Don't try to add all different report views at once, build your dashboard one step at a time.

Avatar
Joseph Horling
Member
Members
Level 0
Forum Posts: 15
Member Since:
June 24, 2018
sp_UserOfflineSmall Offline
7
October 20, 2019 - 11:10 pm
sp_Permalink sp_Print

Hi Anders,

I think I will take your advice and scrap my original  data and try to make it more what you suggest to do.  After I get it improved I would appreciate it if you can take a look at it again.  Thanks, Joe

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Amin Khajeh
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Lawrence Miu
Jo Saies
Paul Pritchard
Monique Roussouw
Keith Aul
Richard Dunks
Manjula Mangalmurti
Thomas Quidort
Jamie Preece
Bob Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6192
Posts: 27146

 

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

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • 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
 

Company

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

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.