Timesheet Calculation

farhad

New Member
Joined
May 18, 2009
Messages
29
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
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Joined
Jul 4, 2004
Messages
7,484
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:

farhad

New Member
Joined
May 18, 2009
Messages
29
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
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top