Timesheet Calculation

[farhad]

Good Day

I have this this Spreadsheet that calculates time

This is the format I have

Start Time Lunch Taken End Time Hours worked
7:50am 00:00 11:45am 3:55
4:45pm 00:00 7:30pm 2:45
8:05am 00:00 11:30am 3:25
7:55am 00:00 11:45am 3:50
4:45pm 00:00 10:30pm 5:45
7:15pm 00:00 9:00pm 1:45
7:55am 00:00 11:45am 3:50

total hours worked: 25:15

If I manually calculate the hours worked I get a figure of 25:25

I posted the above a previous forum and advised to change the format of the total hours worked which did not work

Start time: Format hh:mm PM/AM
Lunch time: format [h]*Hrs. and *m*Mins.*
End time Format hh:mm PM/AM
Hours worked Format: [h]:mm
total hours worked format: [h]:mm

Thanks

 

[MARK858]

Re: Time SHeet Calculation

Are you sure that your manual calculation is correct? I get....

Excel Workbook
	A	B	C
1	7:50am 00:00 11:45am 3:55	3	55
2	4:45pm 00:00 7:30pm 2:45	6	40
3	8:05am 00:00 11:30am 3:25	10	5
4	7:55am 00:00 11:45am 3:50	13	55
5	4:45pm 00:00 10:30pm 5:45	19	40
6	7:15pm 00:00 9:00pm 1:45	21	25
7	7:55am 00:00 11:45am 3:50	25	15
Sheet1

 

[jim may]

Re: Time SHeet Calculation

Not sure what you are after.... But could you use this template? I do and have for months
on end, and I love it!!! ONLY Highlighted (Yellow) Cells for INPUT (B4:C4) and (C8:F14)
--- Jim

Excel 2010

	A	B	C	D	E	F	G
	Employee Details:	James May
	Manager Details:
	Total Work Week Hours	Total Hours Worked	Regular Hours	Overtime Hours
	31.00	31.00	31.00	0.00
	Dates And Day of Week	Time In	Lunch Start	Lunch End	Time Out	Hours Worked
	8:55 AM	Lunch Start	Lunch End	6:35 PM
	Time In	Lunch Start	Lunch End	Time Out
	9:15 AM	Lunch Start	Lunch End	6:30 PM
	8:30 AM	Lunch Start	Lunch End	1:00 PM
	9:15 AM	12:00 PM	1:10 PM	6:00 PM
	Time In	Lunch Start	Lunch End	Time Out
	Time In	Lunch Start	Lunch End	Time Out

<tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #C28D8E"]Time Sheet[/TD]
[TD="bgcolor: #C28D8E"][/TD]
[TD="bgcolor: #C28D8E"][/TD]
[TD="bgcolor: #C28D8E"][/TD]
[TD="bgcolor: #C28D8E"][/TD]
[TD="bgcolor: #C28D8E"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]4/8/2019[/TD]
[TD="align: center"]4/12/2019[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2D3D3"]04/08/19 - Monday[/TD]

[TD="align: center"]9.67[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #E2D3D3"]04/09/19 - Tuesday[/TD]

[TD="align: center"]0.00[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #E2D3D3"]04/10/19 - Wednesday[/TD]

[TD="align: center"]9.25[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #E2D3D3"]04/11/19 - Thursday[/TD]

[TD="align: center"]4.50[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #E2D3D3"]04/12/19 - Friday[/TD]

[TD="align: center"]7.58[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #E2D3D3"]04/13/19 - Saturday[/TD]

[TD="align: center"]0.00[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #E2D3D3"]04/14/19 - Sunday[/TD]

[TD="align: center"]0.00[/TD]

[TD="align: center"]15[/TD]

</tbody>

Time Sheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B6[/TH]
[TD="align: left"]=C6[/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=SUBTOTAL(109,TimeSheet[Hours Worked])[/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]=IFERROR(IF(C6<=WorkweekHours,C6,WorkweekHours),"")[/TD]
[/TR]
[TR]
[TH]E6[/TH]
[TD="align: left"]=IFERROR(C6-D6, "")[/TD]
[/TR]
[TR]
[TH]B8[/TH]
[TD="align: left"]=TEXT(B$4,"mm/dd/yy") & " - " & TEXT(B$4,"dddd")[/TD]
[/TR]
[TR]
[TH]G8[/TH]
[TD="align: left"]=IFERROR(IF(COUNT(TimeSheet[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Lunch End]]+[@[Lunch Start]]-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH]WorkweekHours[/TH]
[TD="align: left"]='Time Sheet'!$B$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[farhad]

Re: Time SHeet Calculation

Hi mark

My manual calculation is as follows

3:55
2:45
3:25
3:50
5:45
1:45
3:50

Method I used

Total Hours = 20 hours
Total Minutes = 315 minutes
Convert 315 minutes to hours = 315/60 = 5 hours 25 minutes
Add 20 hours to 5 hour =25 hours and 25 minutes

 

[MARK858]

Re: Time SHeet Calculation

315/60 = 5.25 not 5 hours 25 minutes. .25 is decimal i.e 25/100 or 1 quarter and a quarter of an hour is 15 minutes.

Do =315/1440 and format as [hh]:mm