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

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

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.

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.