Hello,

Your attachment seems to be without data.

If you haven’t already, do check out this article, it contains good info. https://www.myonlinetraininghub.com/excel-time-calculation-tricks

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.

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.myonlinetraininghub.com/excel-tabular-data-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.

 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.

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.

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