Unsocial Hours Time Sheet Calculations

[aproberts1980]

I am trying to build a sheet to calculate unsocial hours pay from a timesheet. The criteria for the unsocial hours is as follows:

Monday - Friday 0700-2000 - No enhancement
Monday - Friday 1900-0700 - 44%
Saturday - 0700-0700 Sunday 44%
Sunday - 0700-0700 Monday 88%

example -

Mon - Fri 07:00-19:00 would pay 12 hrs standard
Mon - Fri 15:00 - 23:00 would pay 5 hrs standard and 3 hrs +44%
Mon - Fri 19:00-07:00 would pay 12 hrs +44%
Saturday 07:00-19:00 would pay 12 hrs +44%
Sunday 07:00-19:00 would pay 12 hrs +88%

Up to and including the hours column we have as core data so it is just the forumla needed to read to hours into the relevant category that is needed

Location	Name	Date	Start	End	Hours	Standard	44%	88%
test1	subject1	14/07/2020	15:00	23:00	8	5	3	0
test1	subject2	14/07/2020	19:00	07:00	12	0	12	0
test1	subject3	18/07/2020	07:00	19:00	12	0	12	0
test1	subject4	19/07/2020	19:00	07:00	12	0	0	12

Any help that could be given would be appreciated.

 

[Eric W]

Welcome to MrExcel!

A quite tricky problem, which is why no one's answered yet. Here's my stab at it:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Start	End	Type		Location	Name	Date	Start	End	Hours	Std	Rate44	Rate88		Start	End
2	Sunday 0:00	0	0.291667	Rate88		test1	subject1	14-Jul	15:00	23:00	8	4	4	0		2.625	2.958333333
3	Sunday 7:00	0.291667	0.791667	Rate88		test1	subject2	14-Jul	19:00	7:00	12	0	12	0		2.791666667	3.291666667
4	Sunday 19:00	0.791667	1.291667	Rate88		test1	subject3	18-Jul	7:00	19:00	12	0	12	0		6.291666667	6.791666667
5	Monday 7:00	1.291667	1.791667	Std		test1	subject4	19-Jul	19:00	7:00	12	0	0	12		0.791666667	1.291666667
6	Monday 19:00	1.791667	2.291667	Rate44		test2	subject5	13-Jul	0:00	10:00	10	3	0	7		1	1.416666667
7	Tuesday 7:00	2.291667	2.791667	Std		test2	subject6	13-Jul	5:00	20:45	15.75	12	1.75	2		1.208333333	1.864583333
8	Tuesday 19:00	2.791667	3.291667	Rate44
9	Wednesday 7:00	3.291667	3.791667	Std
10	Wednesday 19:00	3.791667	4.291667	Rate44
11	Thursday 7:00	4.291667	4.791667	Std
12	Thursday 19:00	4.791667	5.291667	Rate44
13	Friday 7:00	5.291667	5.791667	Std
14	Friday 19:00	5.791667	6.291667	Rate44
15	Saturday 7:00	6.291667	6.791667	Rate44
16	Saturday 19:00	6.791667	7.291667	Rate44
17	Sunday 7:00	7.291667	7.791667	Rate88
18	Sunday 19:00	7.791667	8.291667	Rate88
19	Monday 7:00	8.291667	8.791667	Std
Sheet6

Cell Formulas
Range	Formula
C2	C2	=7/24
K2:K7	K2	=(IF(J2<I2,J2+1,J2)-I2)*24
L2:N7	L2	=ROUND(SUM(IF($D$2:$D$19=L$1,IFERROR(EXP(LN(IF($Q2<$C$2:$C$19,$Q2,$C$2:$C$19)-IF($P2>$B$2:$B$19,$P2,$B$2:$B$19))),0)))*24,2)
P2:P7	P2	=WEEKDAY(H2)-1+I2
Q2:Q7	Q2	=P2+K2/24
B3:B19	B3	=C2
C3:C19	C3	=B3+0.5
Press CTRL+SHIFT+ENTER to enter array formulas.

First you'll note the table on the left. You can put this anywhere, even on another sheet. It has the time ranges for the whole week, the start time, end time, and type. Since the ranges were all 12 hours apart, I just entered the first 2 manually, and used formulas the rest of the way down. Note that Sunday is day 0 in this scheme, and each day goes up by 1. Also note that your table as you described it overlaps. For weeknights, the normal range ends at 20:00, but the 44% range starts at 19:00. You may need to adjust the table to whatever that hour really is, and you might need to do it manually.

Next, look at columns P and Q. These convert the start and end times from the H/I/J columns into the ranges that match the table. You can hide these columns, or if you want we can incorporate them into the final formula and get rid of them. But it was easier to test with them like this, and it keeps the final formula shorter.

Finally the L2 formula should do what you want. Put it in L2, then copy to the rest of the L2:N7 range. The headings in L1:N1 can be whatever you want, they just have to match column D. Note that in this example L2 and M2 are different from your expected values, but that's due to the 19:00/20:00 issue I just mentioned. This also assumes no one will have a shift over 24 hours. If so, enter it in 2 pieces.

Take a look and see if this might work for you!

 

[aproberts1980]

Thanks for this, it is really appreciated. The main concept makes sense but the big formula when copied to excel returns 0. I have attached an image below. formula below is taken from L2.

=ROUND(SUM(IF($D$2:$D$19=L$1,IFERROR(EXP(LN(IF($Q2<$C$2:$C$19,$Q2,$C$2:$C$19)-IF($P2>$B$2:$B$19,$P2,$B$2:$B$19))),0)))*24,2)

Also if there was a time change in the criteria of when the allowance starts and ends do I just change this in the left table?

Thanks again for you help.

 

[KRice]

Nice job, Eric. I had begun to make a similar mapping of time blocks over an entire week, but I hadn't yet sorted out the complete cycle..

Regarding your questions @aproberts1980, did you enter the L2 formula as an array formula (confirmed with Ctrl-Shift-Enter)? The time blocks would be edited in columns A, B, C, and D.

 

[Eric W]

Hi aproberts1980,

I think Kirk is right when he said that you need to enter the formula as an array formula. If you're not familiar with array formulas, after you enter the formula in the formula bar, hold down the Control and Shift keys, then press Enter. Once you do that, you can copy it and paste it like a normal formula.

He's also correct about editing the table. Just change the start and end times as needed. There's no reason that each range is exactly 12 hours, they can be longer or shorter, just make sure that the ranges do not overlap. If you need to add or remove some lines, you can, you just need to update the ranges in the big formula.

Another thing! You're new here, so you're probably not familiar with the tool I used to post my screen print. It's called XL2BB, and you can get a copy by clicking on the XL2BB button in the response box, or my signature. It makes it much easier for someone to look at your spreadsheet. But even if you don't have it, it's got a feature that you can use. Look at post #2 where I put my screen print:

If you click on the icon I circled, it will copy the sheet to the clipboard. If you then go to a blank Excel sheet and click Paste, it will copy the whole thing, including formatting and formulas to your sheet. I looked at your regular screen print in post 3 and thought "I hope he didn't have to type all that!". The exception is array formulas. It will copy the main formula, but you still have to edit the formula again to add the Control+Shift+Enter.

Anyway, let us know if you have any more questions!

 

[Sulprobil]

If you require this to be solved by worksheet functions, it is fairly tricky.
If you allow VBA, I have seen at least two obviously well tested functions on the web.
Problem is, in contrast to German speaking forums the English speaking ones (I know of) do not allow links.

 

[MARK858]

Problem is, in contrast to German speaking forums the English speaking ones (I know of) do not allow links.

Do you mean against the rules to post links or technically can't post the links.

but you still have to edit the formula again to add the Control+Shift+Delete.

@Eric W, I think that you made a typo in post #5 and meant Control+Shift+Enter in the line above (it is correct earlier in the post).

 

[Sulprobil]

The rules do not allow it.

 

[MARK858]

The rules do not allow it.

That is not 100% accurate, the rules state

So if it is an article then sometimes they are acceptable. If unsure it can always be checked with a Moderator whether a particular link is allowable or not.

 

[Sulprobil]

Ok, I think they qualify as articles, if the moderators agree:

https://www.reddit.com/r/excelevator/comments/cf7d1p

and

sbTimeDiff | Sulprobil

berndplumhoff.gitbook.io