Help needed to create complex timesheet in Excel 2016

[Shazia03]

Hi All

I'm new to the forum an Excel. I employ a number of carers that I’ve got to create a timesheet for. Yes I’m disabled and have a number of carers that work different shifts at different rates for me. I used to do it by hand but find it difficult to write now. So I'm trying to computerise it.


I've got to create an excel timesheet spreadsheet. Now I’m new to Excel 2016 Mac and am teaching myself with a book.
I can do very basic stuff but creating timesheets is beyond me and my book.

I was hoping someone with more experience is kind enough to help me.

I need to include the time they started, the finish time and the total hours for the day. The timesheet needs to show that there are 2
different shifts day and night. Each shift has a different pay rate £9 and £10.50.
Now if it's a bank holiday the pay rate is doubled.
I need to be able to show sick leave and vacation leave.
And finally calculate the total hours for the month and pay for each shift, sick or vacation leave.

WHERE DO I EVEN START!!!


I’ve sort of started but it’s not good if I’m doing all the calculations in my head. I’ve tried to upload it as attachment but it’s not working.

Please can someone help me.

Thanks
Shazia03

 

[Shazia03]

OK, I have to admit this had me stumped for a bit however I think I've sorted it.

Can you PM me with your email address and I'll send you the rough sheet and if I have got the essence of what you need then I can tart it up?

One of the key challenges was that when considering how to cut up the shifts into day and night the fact that you can't always for finish-start even for simple "only in the night" shifts because midnight gets in the way was really bugging me. To get rid of this I added 1 hour to everything to move the day/night threshold to midnight.
The only issue with this is that normally midnight (24:00) has a value of 1 (type it in and then copy / pastes special: Values) however if you add 1 hour to 23:30 (i.e. 00:30) using the sum 23:00+"1:00" = 00:30 Excel sees the result as 1.0020833 which is a bit of a swine as if you then try the simple math of 07:00-0:30 it gets upset because what Excel sees is 0.291666666666667 - 0.291666666666667 which is of course negative time. To avoid this we use the sum
IF(start+"1:00">1,start+"1:00"-1,start+"1:00")) i.e. if start+1 hour is greater than 1 do the sum and then take 1 away otherwise just do the sum.

Then the next challenge was that shifts can start and finish in different shift zones:
* Night - Day = simple, no one is going to work more than 24 hours so there's only one calculation for day and night here
* Day - Night = Again simple for the same reason
* Night - Night = more complex and someone could simply be working a normal night shift HOWEVER then could also be starting in the night, working through the day and the clocking off at night
* Day - Day = the same complex situation occurs.

Once we've got calculations for day and night times for each of these situation we then use a nested If statement along the lines of the below for each of the day and night hours and display in two columns
IF(n-d", Calculation, IF("d-d", Check for simple or complex and do relevant calc , IF("n-n", check for simple or complex and do relevant calc, IF("d-n", Calculation,"")))))

Once you've got the times you can *24 to turn them into digital hours and multiply by the day or night rate.

But wait! We've got to worry about the start being a bank holiday or not. That's not difficult - just list the bank holidays in a column and name that range "Bank_holidays". Have a column of start dates (don't care about the date they clocked off). Now we can have a column called Bank Holiday? with the calculation
=IF(ISERROR(VLOOKUP(A2,Bank_holidays,1,FALSE)),"","BH")
This is looking in the column of dates you called "bank_holidays for the start date -if it isn't there an error will result from the VLOOKUP - this will be caught by the ISERROR command and so the IF will be true - then the calculation will put nothing in the cell, if on the other hand the start date is in the column of bank holidays the VLOOKUP will return the date, so there will not be an error, the IF is false and the entry "BH" is put into the cell.
Now we know if we need to multiply by 2 the pay.


To get the information to display by month: Use the MONTH command to get the month number and then a vlookup to turn that into something nice to read (so have a pair of columns named "months" which has 1-12 in one column and Jan-Dec in the other. Then use the formula:
=IF(A2="","",VLOOKUP(MONTH(A2),months,2,FALSE)) where the date is in column A

We also have to handle the sick and holiday dates so I have given them a value of 1 in the pay column. This would be an issue if you were just adding up the value in "total pay" however as we're using a pivot we can divide this out.

The end result is a table showing months, sick days, holiday days and total pay for the month.

Refresh the pivot table by clicking into it and right mouse / refresh

When you've got the sheet let me know if this is the right sort of thing and any changes you want to make.

HTH

Miles

Hi Miles

You do know your speaking in a foreign language to me. It's quite cute really makes me look clever.

my email shaziarashid1@hotmail.co.uk.

• If it helps you can split the shifts up into day and night. For example if there was a shift from 10pm to 8am, you do 10pm-11pm as day then 11pm-8am as night.

If you give me your email I can show you what i've done in the past which i'm trying to improve.

Many Thanks
Shazia

 

[Shazia03]

Hi Miles

You do know your speaking in a foreign language to me. It's quite cute really makes me look clever.

my email shaziarashid1@hotmail.co.uk.

• If it helps you can split the shifts up into day and night. For example if there was a shift from 10pm to 8am, you do 10pm-11pm as day then 11pm-8am as night.

If you give me your email I can show you what i've done in the past which i'm trying to improve.

Many Thanks
Shazia

Hi Miles

I’ve had a look at the timesheet. I have to admit when I first saw it I thought no way can I understand what-go-on-here.

But at closer inspection and trying it out I sort of figured it out.

· I don’t know what everything highlighted in yellow does.

· When you mark something as leave they get paid at that time rate. All annual leave is paid. Same for sick.

· Is there any way you can simplify it so at the end I just have figures and a monthly round up I can send to the accountant. Something like the timesheet I’ve attached which I found on the net. This template doesn’t have everything I need but I like the layout in weeks and then monthly round up.
· CAN YOU DO SOMETHING LIKE THIS?

· I’ve sent you a email to which I've attached quite a few timesheet templates that I found on the internet. I thought it might help you as they contain a lot of what I want.

Thanks
Miles

 

[miless2111s]

before playing with the format etc do the calculations work as you expected?

 

[Shazia03]

before playing with the format etc do the calculations work as you expected?

Hi Miles

I've had a play around with the timesheet and it seems to be calculating the basic rates and times properly from what I can tell.

• There's one more pay rate I forgot to mention the supervisor gets a flat rate of £11 day and night. There's only 1 supervisor Soraya Mohammed.
• What's next?
• I was thinking if there was a dropdown box listing "Type of Day" - Reg Day, Reg Night, BH Day, BH Night, Sick Day, Sick Night, Vacation Day, Vacation Night. What ever is chosen you calc at that rate.
• This sounded better in my head not to sure now but I'll leave it up to you.

Thanks
Shazia

 

[miless2111s]

Shazia

Um.... little confused The examples so far and the calculation split the day up into Day / Night and then doubles if BH. But now you're talking about a drop down to drive the calculation as if the whole shift should be categorised as day /night. Which is it?

In terms of the supervisor that's easy and they'll have their own sheet as you want one sheet per person. Their sheet will have a single charge rate of £11, no worries

Regards

Miles

 

[Shazia03]

Shazia

Um.... little confused The examples so far and the calculation split the day up into Day / Night and then doubles if BH. But now you're talking about a drop down to drive the calculation as if the whole shift should be categorised as day /night. Which is it?

In terms of the supervisor that's easy and they'll have their own sheet as you want one sheet per person. Their sheet will have a single charge rate of £11, no worries

Regards

Miles

Hi Miles

Sorry i'm confusing you for a change.

I was just trying to figure out how we state what type of day it is. As they get paid annual leave (vacation) which will be paid day/night rate depending on what times they allocate off as vacation. For example PA could take the whole of Monday off as annual leave. On Monday they would usually work 11am-12.30pm and again 11pm-8am. So Monday they would have 2 shifts off but still get paid for them. Therefore PA would get annual leave for day shift 11-12:30pm @ day rate and then also have 11pm-8am night shift off paid @ night rate.


So I was trying to figure out some way of incorporating this but like i say your the expert.

Shazia