IF AND OR nested

[AndyB63UK]

Im trying to calculate work time and overtime. The basic hours are 8, overtime at time and a half is between 8 hours and 12 hours, and double time is over 12 hours. so if the total hours worked in G7 that day are 14:30, then how do I get cells Basic G8 = 8:00, OT 1.5 G9 = 4:00 and OT 2.0 G10 = 2:30. or it only 7:15 total in G7 then G8 = 7:15, G9 = blank, G10 = blank?

Hope that makes sense

 

[Peter_SSs]

See if these, copied across, do what you want.

Excel Workbook
	F	G	H	I
7	Total	14:30	7:15	10:23
8	Normal	8:00	7:15	8:00
9	OT(x1.5)	4:00		2:23
10	OT(x2)	2:30
OT

 

[AndyB63UK]

I can't seem to paste a copy of my sheet into here, but the column headings are;

Date, Start time, break 1, break 2, break 3, finish time, total hours, basic, OT 1.5, OT 2
Mon 1,
Tue 2,
Wed 3,
Thu 4,
Fri 5,
Sat 6,
Sun 7,

Saturday is always OT1.5 for first 8 hours the OT2
Sunday is always OT2 regardless

Thanks

 

[Peter_SSs]

Apart from the weekend issue that you have now added, did the previous formulas do what you wanted? If not which one(s) gave the wrong answers and what should the correct answers have been?

Does your Date column contain actual dates (numbers) or just text?

For posting small sample screen shots like I did, read my signature block below.

 

[AndyB63UK]

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Breaks[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Finish[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Basic[/TD]
[TD="align: center"]OT 1.5[/TD]
[TD="align: center"]OT 2.0[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]1st[/TD]
[TD="align: center"]2nd[/TD]
[TD="align: center"]3rd[/TD]
[TD="align: center"]Time[/TD]
[TD="align: center"]Hours[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]12:00[/TD]
[TD="align: center"]>12:00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="bgcolor: #E2EFDA, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FCE4D6, align: center"]Totals[/TD]
[TD="bgcolor: #FCE4D6, align: center"][/TD]
[TD="bgcolor: #FCE4D6, align: center"][/TD]
[TD="bgcolor: #FCE4D6, align: center"][/TD]
[TD="bgcolor: #FCE4D6, align: center"][/TD]

</tbody>

Sheet1​

 

[AndyB63UK]

Yes those formulas now work thank you, apart from the weekend issue.
The dates are formatted as ddd dd but taken from Week Ending, so A7 is =$H$2-6, A8 =$H$2-5 etc

I have another question relating to this sheet. Is there a way to enter time as just 4 digits without the colon AND without VBA. eg. for 08:45 just type 0845 or even just 845 and it be recognised as 08:45? I know this can be don with VBA code but that means a macro enabled file which the android excel app does not recognise or is able to open.

 

[Peter_SSs]

Yes those formulas now work thank you, apart from the weekend issue.

Try these
H7: =MIN(G7,1/3*(WEEKDAY(A7,2)<6))
I7: =IF(WEEKDAY(A7,2)=7,0,IF(G7>H7,MIN(G7-H7,1/6+1/3*(H7=0)),0))
J7: =IF(G7>SUM(H7:I7),G7-SUM(H7:I7),0)

Is there a way to enter time as just 4 digits without the colon AND without VBA.

Not as far as I am aware.

 

[Dave Patton]

A quick example that you can experiment with.
I have never used this approach on a working time sheet but it may help you.

I have not used your cell references.

Excel 2010
	J	K	L	M	N
1	Start	End	Time
2	730	1200	04:30:00	Format as Time
3
4	or
5
6	800	2230	14.50	Format number
7
8	Regular		8.00
9	OT @ 1.5		4
10	OT @ 2.0		2.5
11
1a

Cell Formulas
Range	Formula
L2	=TEXT(K2,"00\:00")+0-TEXT(J2,"00\:00")+0
L6	=(TEXT(K6,"00\:00")+0-TEXT(J6,"00\:00")+0)*24
L8	=MIN(8,L6)
L9	=IF(L6>L8,MIN(L6-L8,4))
L10	=IF(L6>12,L6-12,0)

 

[AndyB63UK]

Try these
H7: =MIN(G7,1/3*(WEEKDAY(A7,2)<6))
I7: =IF(WEEKDAY(A7,2)=7,0,IF(G7>H7,MIN(G7-H7,1/6+1/3*(H7=0)),0))
J7: =IF(G7>SUM(H7:I7),G7-SUM(H7:I7),0)

Do I change these for rows 12 Saturday and 13 Sunday as row 7 is Monday etc?

 

[AndyB63UK]

Try these
H7: =MIN(G7,1/3*(WEEKDAY(A7,2)<6))
I7: =IF(WEEKDAY(A7,2)=7,0,IF(G7>H7,MIN(G7-H7,1/6+1/3*(H7=0)),0))
J7: =IF(G7>SUM(H7:I7),G7-SUM(H7:I7),0)

These work fine. Many thanks for your help.