# Timesheet Calculation

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### MARK858

##### MrExcel MVP
Re: Time SHeet Calculation

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

Excel Workbook
ABC
17:50am 00:00 11:45am 3:55355
24:45pm 00:00 7:30pm 2:45640
38:05am 00:00 11:30am 3:25105
47:55am 00:00 11:45am 3:501355
54:45pm 00:00 10:30pm 5:451940
67:15pm 00:00 9:00pm 1:452125
77:55am 00:00 11:45am 3:502515
Sheet1

#### jim may

##### Well-known Member
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
ABCDEFG
1Time Sheet
2Employee Details:James May
3Manager Details:
44/8/20194/12/2019
5Total Work
Week Hours
Total Hours
Worked
Regular HoursOvertime Hours
631.0031.0031.000.00
7Dates And Day of WeekTime InLunch StartLunch EndTime OutHours Worked
804/08/19 - Monday8:55 AMLunch StartLunch End6:35 PM9.67
904/09/19 - TuesdayTime InLunch StartLunch EndTime Out0.00
1004/10/19 - Wednesday9:15 AMLunch StartLunch End6:30 PM9.25
1104/11/19 - Thursday8:30 AMLunch StartLunch End1:00 PM4.50
1204/12/19 - Friday9:15 AM12:00 PM1:10 PM6:00 PM7.58
1304/13/19 - SaturdayTime InLunch StartLunch EndTime Out0.00
1404/14/19 - SundayTime InLunch StartLunch EndTime Out0.00
15

<tbody>
</tbody>
Time Sheet

Worksheet Formulas
CellFormula
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
NameRefers To
WorkweekHours='Time Sheet'!\$B\$6

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

##### New Member
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

##### MrExcel MVP
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

Last edited:

Replies
1
Views
75
Replies
3
Views
85
Replies
5
Views
65
Replies
3
Views
326
Replies
8
Views
270

1,133,243
Messages
5,657,575
Members
418,401
Latest member
B_A_M155

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back