Formula for work and overtime

[Alizakore]

Hello,
I have a simple file where i put my times for work and the overtime. It looks like this:

2017-10-26 14:00-23:00 (30)

(30) is the overtime. I want a code that writes this for me. So the standard-time is 14:00-22:30 and any deviations from this will show in another column.

Thanks!

 

[Manuel_Spreadsheet]

1. Key the standard end time (22:30) into a cell on your worksheet (I used A1 to test this).
2. Assuming your end time is in column C, use the following formula.

=MAX(0,C2-A1)

3. Format the cell as "hmm" and it will display as "030"

 

[Logit]

.
I took a different approach. Assuming you are paid for 8 hours work regular time with a 30 minute meal break. So anything over 8.5 hrs is overtime.

Also assuming you are willing to change the format you enter your times ... from 14:00 ... change to 14.00 or instead of 23:30 ... change to 23.5
(a quarter hour would be .15 ...... 3/4 hour would be .45)


Excel 2007 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​	DATE​	START​	END​	TOTAL​	OVERTIME​
2​	2017-10-26​	14.00​	23.00​	8.50​	0.50​		Format Columns B:D as NUMBER / 2 decimals
3​	2017-10-27​	14.00​	22.50​	8.00​
4​	2017-10-28​	14.00​	23.00​	8.50​	0.50​		Formula paste in D2 and drag down : =IF(B2="","",IF(C2="","",(C2-B2)-0.5))
5​	2017-10-29​	13.00​	23.00​	9.50​	1.50​
6​	2017-10-30​	14.00​	23.00​	8.50​	0.50​		Formula paste in E2 and drag down : =IF(D2<8,"",IF(D2>8,D2-8, ""))

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>