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

 

[miless2111s]

Happy to help.

1) how much do you want to automate; i.e. do you want the spreadsheet to 'know' that the shift is a day or night shift or will you be happy to put in "day" or "night"?
2) likewise for the bank holidays
3) are you happy with vlookup? This would be the easiest way to have the sheet put in the rate:
a) for a given shift you have either a calculation or a manual entry which puts "day" / "night" / "Bank holiday" into a column (lets say column D).
b) else where in the workbook you have a pair of columns with the shift (day/night/BH) and then the rate in the next column (9/10/20)
c) this pair of columns is called "rate" (select the range, go to the top left and type in the name rate and hit return
d) in E2 have the formula =vlookup(d2,rates,2,false) - this translates as look at D2, remember it and go look at the range "rates", look down the first column and find what is in D2, then count 2 columns across and report what you find - i.e. the rate for the entry in D2

4) Now we need to deal with adding and taking time away. This is easy if you're always avoiding going across the midnight boundary - just put start and finish times as 24 hours (09:00 or 17:00) and take one from the other. I suspect however that you'll be needing to deal with midnight. To do this type 24:00 in a cell somewhere out of the way and call this "midnight". Then assuming you have start and finish in columns C and D with the total breaks taken in column E your calculation in F can be:
=IF(OR(C2="",D2=""),"",IF(D2>C2,D2-C2-E2,midnight-C2+D2-E2))
Translation:
First check to see if a calculation should be done at all: if (either start or finish are blank) then put a blank in F
Next check to see what sort of calculation we need: if(Finish is larger than start) then is a simple take away otherwise it's a bit more clever.
If simple: Finish - Start - breaks
If the time goes over midnight then: Midnight (24:00) - start plus the bit in the next day - breaks.

Now 24h times are no real use in calculations however they're very useful when thinking about time - not many people know that 2.6666 hours is 2:40. To convert the 24h time into digital time which can be used to calculate pay multiply it by 24.

Then you can use a pivot table to see who should be paid what.

I set up a quick and dirty sheet with the following columns:
A = Who
B = Date
C = Start
D = Finish
E = Breaks
F = Total
G = Shift type
H = Digital Time
I = Pay
J = Month

A2 to E2 = Manually entered
F2 = =IF(OR(C2="",D2=""),"",IF(D2>C2,D2-C2-E2,midnight-C2+D2-E2))
G2 = Manually entered
H2 =IF(OR(F2="",NOT(ISNUMBER(F2))),"",F2*24)
I2 =IF(OR(H2="",G2=""),"",H2*VLOOKUP(G2,Rates,2,FALSE))
J2 =IF(B2="","",VLOOKUP(MONTH(B2),months,2,FALSE))

Copy A2 to J2 down as many rows as you want - make it more than you need

Also I have L1 = 24:00 and called "midnight"
N = Shift with entries Day | Night | BH Day | BH Night in N2-4
O = Rate with entries 9 | 10 | =O2*2 | =O3*2 in O2-4
N:O are called "rates"

Q1 = Number with entries 1 to 12 in Q2-13
R1 = Name with entries Jan to Dec in R2-13
Q1:R13 are called "months"

Then have a pivot based on A:J which groups by Names then Months and sums Pay in the data section.

HTH

Miles

 

[Shazia03]

Hi Miles211s

Really nice to meet you and thank you for replying.

To tell you the truth I don't understand what your say much. As I said I'm a beginner but that's probable an insult to beginner.

Sorry I don't know what pivat tables are or how to use them. I heard of vlookup but again don't know how to use.

• I was thinking like my spreadsheet I need to enter: Time In, Time Out, Day Hours, Night Hour, Overtime Hrs, Vacation Hrs, Sick Hrs, Total Pay.
• What I was thinking is that you enter Start Time and Finish Time then it automatically calculates hours.
• There are no breaks you get paid breaks so included in time.
• There some sort of dropdown box with options Regular Day, Regular Night, Bank Holiday Day, Bank Holiday Night. Now this dropdown box is linked to something that lists the different rates. When you pick an option depending what you pick it will automatically calculate the pay at that rate.

This is as far as I've got.

I will be real grateful for you help

Thanks Shazia

Happy to help.

1) how much do you want to automate; i.e. do you want the spreadsheet to 'know' that the shift is a day or night shift or will you be happy to put in "day" or "night"?
2) likewise for the bank holidays
3) are you happy with vlookup? This would be the easiest way to have the sheet put in the rate:
a) for a given shift you have either a calculation or a manual entry which puts "day" / "night" / "Bank holiday" into a column (lets say column D).
b) else where in the workbook you have a pair of columns with the shift (day/night/BH) and then the rate in the next column (9/10/20)
c) this pair of columns is called "rate" (select the range, go to the top left and type in the name rate and hit return
d) in E2 have the formula =vlookup(d2,rates,2,false) - this translates as look at D2, remember it and go look at the range "rates", look down the first column and find what is in D2, then count 2 columns across and report what you find - i.e. the rate for the entry in D2

4) Now we need to deal with adding and taking time away. This is easy if you're always avoiding going across the midnight boundary - just put start and finish times as 24 hours (09:00 or 17:00) and take one from the other. I suspect however that you'll be needing to deal with midnight. To do this type 24:00 in a cell somewhere out of the way and call this "midnight". Then assuming you have start and finish in columns C and D with the total breaks taken in column E your calculation in F can be:
=IF(OR(C2="",D2=""),"",IF(D2>C2,D2-C2-E2,midnight-C2+D2-E2))
Translation:
First check to see if a calculation should be done at all: if (either start or finish are blank) then put a blank in F
Next check to see what sort of calculation we need: if(Finish is larger than start) then is a simple take away otherwise it's a bit more clever.
If simple: Finish - Start - breaks
If the time goes over midnight then: Midnight (24:00) - start plus the bit in the next day - breaks.

Now 24h times are no real use in calculations however they're very useful when thinking about time - not many people know that 2.6666 hours is 2:40. To convert the 24h time into digital time which can be used to calculate pay multiply it by 24.

Then you can use a pivot table to see who should be paid what.

I set up a quick and dirty sheet with the following columns:
A = Who
B = Date
C = Start
D = Finish
E = Breaks
F = Total
G = Shift type
H = Digital Time
I = Pay
J = Month

A2 to E2 = Manually entered
F2 = =IF(OR(C2="",D2=""),"",IF(D2>C2,D2-C2-E2,midnight-C2+D2-E2))
G2 = Manually entered
H2 =IF(OR(F2="",NOT(ISNUMBER(F2))),"",F2*24)
I2 =IF(OR(H2="",G2=""),"",H2*VLOOKUP(G2,Rates,2,FALSE))
J2 =IF(B2="","",VLOOKUP(MONTH(B2),months,2,FALSE))

Copy A2 to J2 down as many rows as you want - make it more than you need

Also I have L1 = 24:00 and called "midnight"
N = Shift with entries Day | Night | BH Day | BH Night in N2-4
O = Rate with entries 9 | 10 | =O2*2 | =O3*2 in O2-4
N:O are called "rates"

Q1 = Number with entries 1 to 12 in Q2-13
R1 = Name with entries Jan to Dec in R2-13
Q1:R13 are called "months"

Then have a pivot based on A:J which groups by Names then Months and sums Pay in the data section.

HTH

Miles

 

[miless2111s]

NO worries; we all start somewhere

In terms of your requirements: Are the shifts split between X hours day and Y hours night and if so what are the cut over times? Or are shifts categorised as wholly day or night and if so what are the rules there?

In terms of learning what the stuff I wrote down is doing I suggest just copying the formulae / titles into the stated cells on a test sheet and looking to see what they're doing...Some of the most powerful learning that I have done is copying other people's work, picking it apart and understanding it and then changing it to check that I've correctly understood

Pivot tables are simple, they often worry people but that's something we can help you through

Where in the world are you (i.e. what time zone)? I'm UK based

Regards

Miles

 

[Shazia03]

Hi Miles

I'm from Manchester UK.

Is there any way I can upload a file to show you what I've done? I've made an attempt at a timesheet but it's very basic where i'm practically doing the calculations myself. I've probably over complicated it.

I would prefer something like your timesheet. You may need to talk me through it step by step from the beginning. i.e
Step 1:
In cells A1 to K1 enter headings Start Date, Start Time, Finish Time, Day Hours, Night Hours, Overtime, Sick, Vacation etc

I know this may take awhile but I don't think I will be able to understand your instruction otherwise.

I will be eternally grateful if your willing to help.

From
Shazia

NO worries; we all start somewhere

In terms of your requirements: Are the shifts split between X hours day and Y hours night and if so what are the cut over times? Or are shifts categorised as wholly day or night and if so what are the rules there?

In terms of learning what the stuff I wrote down is doing I suggest just copying the formulae / titles into the stated cells on a test sheet and looking to see what they're doing...Some of the most powerful learning that I have done is copying other people's work, picking it apart and understanding it and then changing it to check that I've correctly understood

Pivot tables are simple, they often worry people but that's something we can help you through

Where in the world are you (i.e. what time zone)? I'm UK based

Regards

Miles

 

[miless2111s]

Hi Miles

I'm from Manchester UK.

Is there any way I can upload a file to show you what I've done? I've made an attempt at a timesheet but it's very basic where i'm practically doing the calculations myself. I've probably over complicated it.

I would prefer something like your timesheet. You may need to talk me through it step by step from the beginning. i.e
Step 1:
In cells A1 to K1 enter headings Start Date, Start Time, Finish Time, Day Hours, Night Hours, Overtime, Sick, Vacation etc

I know this may take awhile but I don't think I will be able to understand your instruction otherwise.

I will be eternally grateful if your willing to help.

From
Shazia

OK so we're in the same time zone which will be useful

Copy things after the = into the cell reference (so the top left cell is called A1 and so on)
A1 = Who
B1 = Date
C1 = Start
D1 = Finish
E1 = Breaks
F1 = Total
G1 = Shift type
H1 = Digital Time
I1 = Pay
J = Month

Cells A2 to E2 are where you type in the start, finish and break times (if they had breaks that is - which you said not)
F2 = IF(OR(C2="",D2=""),"",IF(D2>C2,D2-C2-E2,midnight-C2+D2-E2))

G2 is where you say if the shift was Day, Night, BH Day or BH night - note the entries need to match what is in the "rates" section you'll add in a bit

H2 =IF(OR(F2="",NOT(ISNUMBER(F2))),"",F2*24)
I2 =IF(OR(H2="",G2=""),"",H2*VLOOKUP(G2,Rates,2,FALSE))
J2 =IF(B2="","",VLOOKUP(MONTH(B2),months,2,FALSE))

Copy A2 to J2 down as many rows as you want - make it more than you need

L1 = 24:00
Name L1 "Midnight" by selecting it and then clicking in the box top left which is currently showing "L1" then just type "midnight". This is the easiest way to name a cell or set (range) of cells

N1 = Shift
N2 = Day
N3 = Night
N4 = NH Day
N5 = BH Night

O1 = Rate
O2 = 9
O3 = 10
O4 =O2*2
O5 = O3*2
Click on N1 and press and hold the Shift Key and then click on O5 - you've now selected the range N1:O5. Name that range "rates" by clicking in that top left box again and typing in "rates"


Q1 = Number with entries 1 to 12 in Q2 to 13 - basically a list of the months of the year in number form
R1 = Name with entries Jan to Dec in R2-13 - basically a list of the names of the months
Name the range Q1:R13 as "months"

You've now got a sheet which calculates the rate for each person and each sheet.

Once you've got that we can walk through a pivot table

Regards

Miles

 

[Shazia03]

Hi Miles

I can't thank you enough for helping me. Your instructions are great I've started work on it but it might take me awhile as I type slow due to my disability.

Some of your field are different to mine if I give you some background information about what my timesheet needs to cover you may understand me better. The following need to be addressed in the timesheet:

· I would like a monthly timesheet.

· I have a number of PA’s that work different shifts at different rates.
· Sometimes PA’s could do more than one shift a day. For example, they could have a shift on Monday 11am-12:30pm, and then they could have another shift the same day Monday 6pm-8:30pm. The timesheet need to show both these shifts separately.

· Each shift has a different pay rate - Day £9 and Night £10.50. Now if it's a Bank Holiday the pay rate is doubled.

· The timesheet needs to show that there are 2 different shifts day and night. Day shift start at 8am-11pm. Night shift start 11pm-8am.

· I need to log a start time and finish time.

· Calculate the Total hours for the day.

· I need to be able to show sick leave and vacation leave.

· And finally calculate the total hours and pay for each shift and sick or vacation leave for the month.


Thank you once again.
Shazia

OK so we're in the same time zone which will be useful

Copy things after the = into the cell reference (so the top left cell is called A1 and so on)
A1 = Who
B1 = Date
C1 = Start
D1 = Finish
E1 = Breaks
F1 = Total
G1 = Shift type
H1 = Digital Time
I1 = Pay
J = Month

Cells A2 to E2 are where you type in the start, finish and break times (if they had breaks that is - which you said not)
F2 = IF(OR(C2="",D2=""),"",IF(D2>C2,D2-C2-E2,midnight-C2+D2-E2))

G2 is where you say if the shift was Day, Night, BH Day or BH night - note the entries need to match what is in the "rates" section you'll add in a bit

H2 =IF(OR(F2="",NOT(ISNUMBER(F2))),"",F2*24)
I2 =IF(OR(H2="",G2=""),"",H2*VLOOKUP(G2,Rates,2,FALSE))
J2 =IF(B2="","",VLOOKUP(MONTH(B2),months,2,FALSE))

Copy A2 to J2 down as many rows as you want - make it more than you need

L1 = 24:00
Name L1 "Midnight" by selecting it and then clicking in the box top left which is currently showing "L1" then just type "midnight". This is the easiest way to name a cell or set (range) of cells

N1 = Shift
N2 = Day
N3 = Night
N4 = NH Day
N5 = BH Night

O1 = Rate
O2 = 9
O3 = 10
O4 =O2*2
O5 = O3*2
Click on N1 and press and hold the Shift Key and then click on O5 - you've now selected the range N1:O5. Name that range "rates" by clicking in that top left box again and typing in "rates"


Q1 = Number with entries 1 to 12 in Q2 to 13 - basically a list of the months of the year in number form
R1 = Name with entries Jan to Dec in R2-13 - basically a list of the names of the months
Name the range Q1:R13 as "months"

You've now got a sheet which calculates the rate for each person and each sheet.

Once you've got that we can walk through a pivot table

Regards

Miles

 

[miless2111s]

Hi Miles

I can't thank you enough for helping me. Your instructions are great I've started work on it but it might take me awhile as I type slow due to my disability.

Some of your field are different to mine if I give you some background information about what my timesheet needs to cover you may understand me better. The following need to be addressed in the timesheet:

· I would like a monthly timesheet.

· I have a number of PA’s that work different shifts at different rates.
· Sometimes PA’s could do more than one shift a day. For example, they could have a shift on Monday 11am-12:30pm, and then they could have another shift the same day Monday 6pm-8:30pm. The timesheet need to show both these shifts separately.

· Each shift has a different pay rate - Day £9 and Night £10.50. Now if it's a Bank Holiday the pay rate is doubled.

· The timesheet needs to show that there are 2 different shifts day and night. Day shift start at 8am-11pm. Night shift start 11pm-8am.

· I need to log a start time and finish time.

· Calculate the Total hours for the day.

· I need to be able to show sick leave and vacation leave.

· And finally calculate the total hours and pay for each shift and sick or vacation leave for the month.


Thank you once again.
Shazia

Happy to help, some follow on questions:
1) I can see from your example that a PA doesn't have to start at a specific time, what happens if the time that they work crosses the day / night time boundary, for instance your if they work 9pm till 1am. Does this get classified as either day or night or is the 9pm- 11pm paid at the day rate and then 11pm - 1.00am si paid at the night rate?

2) I assume you want one sheet to record all work activity on with a column to enter the PA's name? This can be filtered to only show a specific person's entries if you want. Or do you want one sheet per PA?

3) You say that you want to calculate total hours for the day - am I right in assuming in the above example that day 1 = 3h and day 2 = 1h? I ask this seemingly stupid question as I have been on one job where the "day" that the time was booked to was the starting day so in this example Day 1 = 4h and Day 2 = 0

4) What happens with the pay rate when they cross a boundary - so for instance in my example the 2nd day is a bank holiday. Does this mean that 9pm - 11 pm is day, 11pm - 24:00 is night and 0:00 - 1:00am is BH Night or is it more simple than this?

on the slow typing front do you know about the keyboard shortcuts of <CTRL><C> and then <CTRL><V> to copy and paste and tab to move between cells etc - this would mean that you don't have to re-type my formulas?

Regards

Miles

 

[Shazia03]

Happy to help, some follow on questions:
1) I can see from your example that a PA doesn't have to start at a specific time, what happens if the time that they work crosses the day / night time boundary, for instance your if they work 9pm till 1am. Does this get classified as either day or night or is the 9pm- 11pm paid at the day rate and then 11pm - 1.00am si paid at the night rate?

2) I assume you want one sheet to record all work activity on with a column to enter the PA's name? This can be filtered to only show a specific person's entries if you want. Or do you want one sheet per PA?

3) You say that you want to calculate total hours for the day - am I right in assuming in the above example that day 1 = 3h and day 2 = 1h? I ask this seemingly stupid question as I have been on one job where the "day" that the time was booked to was the starting day so in this example Day 1 = 4h and Day 2 = 0

4) What happens with the pay rate when they cross a boundary - so for instance in my example the 2nd day is a bank holiday. Does this mean that 9pm - 11 pm is day, 11pm - 24:00 is night and 0:00 - 1:00am is BH Night or is it more simple than this?

on the slow typing front do you know about the keyboard shortcuts of <CTRL><C> and then <CTRL><V> to copy and paste and tab to move between cells etc - this would mean that you don't have to re-type my formulas?

Regards

Miles

Hi Miles

To answer your questions:

1) Days start from 08:00 am to 23:00 pm. Night start from 23:00 pm to 08:00 am. If a shift is from 9pm to 1am then 9pm-11pm would be classed as day and day rate would be applied, then from 11pm to 1am would be night and payed at night rate.

2) I would like one sheet per PA please.

3) Each shift needs to be logged separately on a row even if it was on the same day. For example a PA could have 2 shifts on Monday each of these shifts will be logged and calculated separately but the date will be the same.

4) I just make the whole day bank holiday normally for example January 1st. I normally make all shift on that day bank holiday starting 8am morning going into night shift 11pm until 8am. I normally calculate this myself as don't know formula. But I'll leave that to you make it as simple or complicated as you like.


Thanks for the keyboard short cut. I use a Mac on screen keyboard so don't know all short-cuts yet nothing works as you expect.

Let me know if you need any more info.

Regards
Shazia

 

[miless2111s]

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