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
1		Time Sheet
2		Employee Details:	James May
3		Manager Details:
4		4/8/2019	4/12/2019
5		Total Work Week Hours	Total Hours Worked	Regular Hours	Overtime Hours
6		31.00	31.00	31.00	0.00
7		Dates And Day of Week	Time In	Lunch Start	Lunch End	Time Out	Hours Worked
8		04/08/19 - Monday	8:55 AM	Lunch Start	Lunch End	6:35 PM	9.67
9		04/09/19 - Tuesday	Time In	Lunch Start	Lunch End	Time Out	0.00
10		04/10/19 - Wednesday	9:15 AM	Lunch Start	Lunch End	6:30 PM	9.25
11		04/11/19 - Thursday	8:30 AM	Lunch Start	Lunch End	1:00 PM	4.50
12		04/12/19 - Friday	9:15 AM	12:00 PM	1:10 PM	6:00 PM	7.58
13		04/13/19 - Saturday	Time In	Lunch Start	Lunch End	Time Out	0.00
14		04/14/19 - Sunday	Time In	Lunch Start	Lunch End	Time Out	0.00
15

<tbody>
</tbody>

Time Sheet​

Worksheet Formulas Cell Formula B6 =C6 C6 =SUBTOTAL(109,TimeSheet[Hours Worked]) D6 =IFERROR(IF(C6<=WorkweekHours,C6,WorkweekHours),"") E6 =IFERROR(C6-D6, "") B8 =TEXT(B$4,"mm/dd/yy") & " - " & TEXT(B$4,"dddd") G8 =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) <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To WorkweekHours ='Time Sheet'!$B$6 <tbody> </tbody>

<tbody>
</tbody>

 

[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