ExcelTip.com
ExcelTip.com
Account Icon Account Icon Account Icon
Google Exceltip.com
JOIN OUR NEWSLETTER
  and receive for joining:
Free eBook Learn More!
Free Excel ADD-IN
Free Weekly Excel Tip
4 e-books in cd-rom
F1 Formulas & Functions
F1 Formulas & Functions
F1 EXCEL
F1 eBook (Spanish)
F1 EXCEL
Financial Statements.xls
 

» Creating a Timesheet
Problem:
Creating a timesheet which calculates the number of hours worked each day and a sum of the total hours worked.

Solution:
Create 3 columns containing the following data: Date, Time In, Time Out.
In the 4th column (Number of Hours), use the following formula:
=(C2-B2+(C2 To sum the total number of hours worked, use the SUM function as shown in the following formula:
=SUM(D2:D7)



Screenshot // Creating a Timesheet

Creating a Timesheet
Rate this tip
12 34 5
  RATING: 3.07
  VIEWS: 48744

READER COMMENTS (view all comments)


How do you make a total hour calculation?
Martin Borcher wrote on June 23, 2005 07:31 EST

Hey,

How do you calculate the total number of hours you have worked - in maybe lets say a week?

When I use the function “=sum()” or “+” I get the wrong number of hours because it starts counting over again after 24h.

Fx. 8h+10h+10h=4h

All help welcome at cabo04ae@student.cbs.dk

ASP

Martin Borcher
Display sum of hours when > 24h
xladdict wrote on June 23, 2005 08:32 EST
Hi Martin

Change your cell formatting from e.g. hh:mm to [h]:mm - this will force Excel to display 46:23 instead of 22:23.
Good examples to create time sheet
michael A. wrote on July 09, 2005 20:56 EST
Thanks you for every thing. Now I can keep in trak on my time card...


Calculating wages on weekly hours
Amanda wrote on July 12, 2005 07:37 EST
Hi I would like to total the amount that is due at the the end of the week based on hourly rate, the problem is it does not seem to acknowledge the minutes on the total, can you help?

Reply to amanda.parga@slam.nhs.uk
Reply: Amanda
Alan wrote on July 13, 2005 20:57 EST
Hi Amanda,

[QUOTE=Amanda]Hi I would like to total the amount that is due at the the end of the week based on hourly rate, the problem is it does not seem to acknowledge the minutes on the total, can you help?

Reply to [email]amanda.parga@slam.nhs.uk[/email][/QUOTE]If thatis correct, then either the minutes were not entered, or more likely, the numbers were rounded (perhaps with a formula) somewhere.

Check the formulae carefully and it should become obvious hopefully.

Alan.
How to take break times into account?
Seano wrote on July 27, 2005 02:13 EST
I'd like to be able to subtract time out of the daily hours for lunch. Ideally by adding another column between time in and time out to keep sheet format logical. Unfortunately the only formula arrangement I can think of based on above is incredibly cumbersome....

Any ideas?
keeping time as number not text to sum
Jennifer wrote on December 31, 1969 19:00 EST
I've calculated the difference in h:mm by using the TEXT(A1-B1,"h:mm") but I need the result to remain as a Number so I can then sum hours for a monthly total.

rona wrote on August 09, 2005 00:45 EST
i have a problem on creating our company's timesheet most specifically on rounding. Based on company policy, overtime should be more than 30 minutes and it should be rounded to 15 suceeding minutes, but if for example you have 44 minutes overtime, it is still considered 30 minutes. What formula can help me to solve my problem. Any idea? Please help me. Thank you!
Reply: Seano
Alan wrote on August 09, 2005 02:42 EST
Hi Seano,

[QUOTE=Seano]I'd like to be able to subtract time out of the daily hours for lunch. Ideally by adding another column between time in and time out to keep sheet format logical. Unfortunately the only formula arrangement I can think of based on above is incredibly cumbersome....

Any ideas?[/QUOTE]Just do something like:

-(TimeIn1-TimeOut1)-(TimeIn2-TimeOut2)

HTH,

Alan.
Help with after midnight timesheet entries
Lee Ann wrote on December 31, 1969 19:00 EST
I need to create a daily timesheet with the start time @ 6pm the end time after midnight sometimes. I also need this sheet to deduct lunch breaks. I can get the formulas for the times before midnight, but not after. I know the basics of excel but not overly experienced with it. Can you help me please, this is starting to make me nuts.

Help with after midnight timesheet entries
Lee Ann wrote on December 31, 1969 19:00 EST
I need to create a daily timesheet with the start time @ 6pm the end time after midnight sometimes. I also need this sheet to deduct lunch breaks. I can get the formulas for the times before midnight, but not after. I know the basics of excel but not overly experienced with it. Can you help me please, this is starting to make me nuts.

Converting
Chuck wrote on December 31, 1969 19:00 EST
Hi
I would like to run my spreadsheet in actual hourly time. e.g. 4:30 as opposed to 1630. What formula can I use to convert the cells to acutal time and then total the columns correctly? Right now when I add 7.30 and 7.30 or seven hours thirty minutes, I get 14.60. and I need it to say 15 hours.

Any help?
Different Time Sheet
WilliamVierra wrote on November 10, 2005 10:50 EST
I did this a slightly different way which seems to produce better results and lets you account for a lunch break.

I use two rows for each day the formula in D6 is =(C6-B6), the formula in D7 is =C7-B7. The formula in E7 is =D6+D7. The formula in E16 is =SUM(E7:E15). The formula in F16 is =E16*24.

Column A6 and A7 have the date in them Column A8 to A15 have the formula =A6+1, =A7+1, =A8+1, =A9+1, =A10+1, =A11+1, =A12+1 and =A13+1.

I have attached an example.
My favorite timesheet template
Gary L Brown wrote on November 11, 2005 09:09 EST
My favorite timesheet has 9 columns A:I.

Col A: [B]Wk# [/B] =WEEKNUM(C3)

Col B: [B]Day[/B] =CHOOSE(MOD(C3,7)
+1,"Sat","Sun","Mon","Tue","Wed","Thu","Fri")

Col C: [B]Date [/B] =IF(OR(MOD(C3,7)<2,MOD(C3,7)=6),C3+CHOOSE(MOD(C3,7)+1,2,1,0,6,5,4,3),C3+1)

Col D: [B]Start [/B] [direct input] 7:48:00 AM

Col E: [B]End [/B] [direct input] 5:45:00 PM

Col F: [B]Lunch [/B] [direct input] .75

Col G: [B]Worked Time[/B] =(MOD((TIME(HOUR(E3),MROUND(MINUTE(E3),15),0)-TIME(HOUR(D3),MROUND(MINUTE(D3),15),0)),1)*24)-F3

Col H: [B]Cum Hrs/Wk[/B] =IF(A3<>A4,SUMIF(A:A,A3,G:G),"")

Col I: [B]Notes: [/B] [direct input] Any notes to explain variances in schedule

This format rounds to the [B]nearest 15 minutes [/B] [Col G] and runs Monday thru Friday [Col C]. If you work Saturday or Sunday, simply entering the correct date in Col C (overriding the formula) will adjust the Wk # and Day.
This format also [B]takes working past midnight into account[/B].
It does NOT allow for working more than 24 hours in a shift.

Row 2 typically needs to have Col A and Col C as direct input. From row 3 and down, the formulas handle all info in Col A, B, C, G, H.

Hope this thread helps.
Sincerely,
Gary Brown
[email]gary_brown@ge_NOSPAM.com[/email]
My favorite timesheet template
Gary L Brown wrote on November 11, 2005 09:20 EST
The topic "My favorite timesheet template" was my first post. If you see /B instead of Bold print, please ignore it.
Thanks,
Gary Brown
timestamp
owl527 wrote on November 11, 2005 23:39 EST
I have a spreadsheet contain a sequence of procedures and I need timestamp for each task being done. how do you get a timestamp at a specific field? currently I am typing the time in, is there a faster way to do it?
timestamp
D. Mckeever wrote on December 31, 1969 19:00 EST
[QUOTE=Owl527]I have a spreadsheet contain a sequence of procedures and I need timestamp for each task being done. how do you get a timestamp at a specific field? currently I am typing the time in, is there a faster way to do it?

Try this... Format the timestamp cells for what you need; i.e. date & time, just the time. Then just use the quick key combo CTRL + ; (semi-colon)

Dave B. McKeever
Reply: owl527
Alan wrote on November 13, 2005 21:39 EST
Hi owl527,

[QUOTE=owl527]I have a spreadsheet contain a sequence of procedures and I need timestamp for each task being done. how do you get a timestamp at a specific field? currently I am typing the time in, is there a faster way to do it?[/QUOTE]Not sure if this is what you mean, but if you select a cell where you want the current time then press Shift-Ctrl-: (colon).

HTH,

Alan.

AMurderOfCrows wrote on January 03, 2006 02:03 EST
Hey all

Needing a timesheet to handle multiple employees with time entered as follows:

830a
945p
1252a

It needs to be able to deduct lunch breaks and caclulate overtime based on a 40 hour work week that starts on a Sunday and ends on a Saturday/Sunday crossover (so if you started your shift on saturday and ended it after midnight, the hours after midnight still count correctly as a Saturday shift).

it ALSO needs to be able to handle pay periods starting on the 1st and on the 16th of each month (this won't effect anything on the timesheet itself, but will effect the Pay calculator i'm trying to tie into it).

I found a great starting point here:

[url]http://www.cpearson.com/excel/overtime.htm[/url]

the problem is that i can't come up with a good way to set the conditions of overtime.

Conditions:
Workweek starts on Sunday Morning (generally around 8am, but can start anytime sunday morning).

Workweek ends on Saturday Night/Sunday Early morning (generally around 3am, but can go a lil bit later sometimes).

Regular Workweek consists of 40 hours. anything over is overtime.

Certain holidays are paid, but don't count for overtime. the Holiday pays for 8 hours Regular Rate (standard shift) if you do not work. The holiday pays the amount of time worked at Double Rate if you work that day. If the holiday falls into overtime, you get Double and a half

ANy help?

AMOC wrote on December 31, 1969 19:00 EST
Hey all

Needing a timesheet to handle multiple employees with time entered as follows:

830a
945p
1252a

It needs to be able to deduct lunch breaks and caclulate overtime based on a 40 hour work week that starts on a Sunday and ends on a Saturday/Sunday crossover (so if you started your shift on saturday and ended it after midnight, the hours after midnight still count correctly as a Saturday shift).

it ALSO needs to be able to handle pay periods starting on the 1st and on the 16th of each month (this won't effect anything on the timesheet itself, but will effect the Pay calculator i'm trying to tie into it).

I found a great starting point here:

http://www.cpearson.com/excel/overtime.htm

the problem is that i can't come up with a good way to set the conditions of overtime.

Conditions:
Workweek starts on Sunday Morning (generally around 8am, but can start anytime sunday morning).

Workweek ends on Saturday Night/Sunday Early morning (generally around 3am, but can go a lil bit later sometimes).

Regular Workweek consists of 40 hours. anything over is overtime.

Certain holidays are paid, but don't count for overtime. the Holiday pays for 8 hours Regular Rate (standard shift) if you do not work. The holiday pays the amount of time worked at Double Rate if you work that day. If the holiday falls into overtime, you get Double and a half

ANy help?
search a particular item thoughout worksheet
Kate wrote on December 31, 1969 19:00 EST
then give me the total hours worked for that projects.

I work for a Arch/Contruction company, each job has it's own project number and name, but it the timesheet there's a breakdown of descriptions, I need grand total throughout timesheet.

Thanks

Kate
Help with Formula for OT on 40 hrs workweek
ijgoesnar wrote on March 10, 2006 14:35 EST
Hi, I am creating Timesheet spreadsheet, I can't figure out the formula to calculate overtime if a person work 7 days in a row (over 40 hours). on the 6th & 7th days, it should be Overtime.

Help
timesheet
Gary L Brown wrote on March 10, 2006 14:52 EST
use my posting from 11-Nov-2005. The formula in Column C calculates weekdays only (Mon, Tues, Wed, etc). When working a weekend, overwrite the formula.

HTH,
Gary Brown
Spreadsheet with actual hourly time
larrynjr wrote on April 09, 2006 11:51 EST
[QUOTE=Chuck]Hi
I would like to run my spreadsheet in actual hourly time. e.g. 4:30 as opposed to 1630. What formula can I use to convert the cells to acutal time and then total the columns correctly? Right now when I add 7.30 and 7.30 or seven hours thirty minutes, I get 14.60. and I need it to say 15 hours.

Any help?[/QUOTE]

Chuck,

Good Day! Here's what we are using : E
A B C D HOURS WORKED
IN OUT IN OUT
Larry 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
Jimmy 7:30 AM 12:00 PM 1:00 PM 4:00 PM 7.50
-----
TOTAL 15.50
Type time with a space followed by A or P after the time then the formula for hours work:

=(D-C-INT(D-C))*24+(B-A-INT(B-A))*24 then Format column E as Number with 1 or 2 decimal places.

Note: Lunch time from 12PM - 1PM is not included. The computed hours is regular time. You can apply the same for overtime hours. If time is 8:45 PM
type 8:45 then space then P

You can add the time of Jimmy and Larry using the Summation icon

I maybe too late for this response, but i shall appreciate sharing with me the responses you receive. Thanks.

Regards,

larrynjr.

rodyzamora wrote on April 13, 2006 04:12 EST
[QUOTE=Jennifer]I've calculated the difference in h:mm by using the TEXT(A1-B1,"h:mm") but I need the result to remain as a Number so I can then sum hours for a monthly total.[/QUOTE]



=(b1-a1)*24 right click format click number
Adding hours to dates
RajinCajun wrote on December 31, 1969 19:00 EST
I was wondering how would you add hours (for example time that it takes to complete a task) with a start date of lets say today 6/2/2006, and figure out the end date. Keep in mind the work week is 4 days long Monday through Thursday and then do the same thing this time using the full seven days of the week. Can this be even done with excel if so can you then edit the formula to exclude holiday dates? Please advice
Requesting help on time sheet to round up to next hour
gycoso3 wrote on June 06, 2006 18:40 EST
I haven't had time to review all of the threads but was wondering if someone would be able to advise me what time of formula or how to edit the one I use to figure hours worked and then round it up to the next our for anything 1 minute past the hour or any amount of time worked. Here is the formula I am using... please help =(F5-D5+(F5<D5))*24 We use multiple columns since we are called out on a regular basis... I might be able to get it if someone get point me in the right direction. Thanks GYCOSO3
How to reverse the calculation|
Ben wrote on December 31, 1969 19:00 EST
Hi,

Not sure where the best to ask this is so i'll do it here.

I have a h:mm time which i need to get converted into days/hours/minutes, creating an on the fly phrase of something like "2 days, 4 hours, 32 mins" for example.

eg: 26:45 (hours/minuts) to be converted to "1 day(s), 2 hours, 45 minutes"...

Any help would be great, email me?
ben@bdesigned.co.uk


Thanks,
Ben
Calculating time after midnight.
Tita wrote on December 31, 1969 19:00 EST
I was trying to calculate the hours worked for a single employee on a specific day. Went crazy trying to get the thing to work. This simple formula worked for me.
Values...
Column A = time in, Column B= Time out (sometimes after midnight)
Column C = Total hours worked.
Make sure all your cells are formatted to the 24 hr clock (13:30)
FORMULA
=(24-A2+B2)

Use the 24 hrs. minus the time in + time out.

It seemed to work for me.

davesexcel wrote on February 24, 2007 13:51 EST
[QUOTE=gycoso3]I haven't had time to review all of the threads but was wondering if someone would be able to advise me what time of formula or how to edit the one I use to figure hours worked and then round it up to the next our for anything 1 minute past the hour or any amount of time worked. Here is the formula I am using... please help =(F5-D5+(F5<D5))*24 We use multiple columns since we are called out on a regular basis... I might be able to get it if someone get point me in the right direction. Thanks GYCOSO3[/QUOTE]

this formula gets the total hours worked and then rounds the total to the nearest 15 min

[CODE]=ROUNDDOWN(((E12<D12) E12-D12 (G12<F12) 2-F12)/TIME(0,15,0),0)*TIME(0,15,0)
[/CODE]


this rounding function will clock you in at the next 15min mark

punch in at 5:49
rounds up to 6:00

[CODE]=ROUNDUP(A1*96,0)/96[/CODE]


and of course this will clock you out at the last 15 mark

punch out at 3:40
rounds down to 3:30

[CODE]=ROUNDDOWN(A1*96,0)/96[/CODE]
conversion of unit of 2sec to mins and hours
stevethank wrote on March 26, 2007 06:44 EST
I have a unit that is 2secs, basically a recording. How do I convert this to mins and hours and check the time at say 200 unit point? The start point of the unit is 7:00AM.
Appreciate any suggestions. Please email to [email]stevethank@yahoo.com[/email]
Thanks
Steve
Overtime Calculating
LizV wrote on May 03, 2007 22:21 EST
I'm trying to create a time sheet that will calculate regular time, 1 1/2, and double time. Here is how it is set up:

Total Time Reg. 1 1/2 Double
20 8 4 8

Is there a way when you input the total time it will break it down?

Thanks for any help with this.

Liz
Calculating Overtime and Double Time Hours based on time of day
philmcracken wrote on May 14, 2007 16:03 EST
I have a situation where i need to track employee labor hours. These employees get paid overtime on any hours worked outside of 8am and 4:30 pm and after working 14 hrs they get double time. I would like to be able to just put in the employees start time and end time and have the spreadsheet calculate their number hours worked and how many of those hours were straight time, overtime and double time.

For example employee starts at 6am and ends at 10pm. I would like to input start time of 6am in column 1 and input end time of 8pm in column two. Then have the spreadsheet calculate straight time hours in column three (8am to 4:30pm = 8.5 straight time hrs), have spreadsheet calculate Overtime hours in column four (6am-8am and 4:30pm to 8pm = 5.5 overtime hours). In column five i want the spreadsheet to calculate double time hours (8pm-10pm = 2 double time hours). And finally sixth column total hours.

Columns 3, 4, & 5 present the challenges to me. Anybody have any solutions?

phil



REGISTERED USERS - Click here to post comments


GUESTS (Click here to register)
Name
Comment Title
Comments

The 11 Immutable Laws of Internet Branding

Special Edition Using Microsoft Excel 2002

422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction

Mastering Excel 2000 (for beginner)

A Mathematician Plays the Stock Market

Windows XP Pocket Reference

RELATED MICROSOFT EXCEL TIPS


Convert PDF Files to Excel






Excel VBA books
Accounting books
Business Plans
MS Office books
Taxes books

VIEW ALL BOOKS


  Advertise With Us                               

Tips

Add-In in VBA | Applications - Word, Outlook in VBA | Array Formulas | Cells, Ranges, Rows, and Columns in VBA | Counting | Custom Functions | Custom Functions in VBA | Database Formulas | Database in VBA | Date & Time Formulas | Date & Time in VBA | Events in VBA | Excel 2003 | Excel Chart | Excel Consolidating | Excel Counting | Excel Custom Functions using VBA | Excel Customizing | Excel Data | Excel Dates | Excel Editing | Excel Files | Excel Filter | Excel Format | Excel Formula | Excel General | Excel Grouping and Outlining | Excel Importing Text Files | Excel Information | Excel Keyboard Shortcuts | Excel Loan Formulas | Excel Macros - VBA | Excel Pivot Tables | Excel Printing | Excel Range Name | Excel Security - Protection | Excel Sorting | Excel Style | Excel Subtotals | Excel Summing | Excel Text | Excel Time | Excel Tools | Excel Worksheet, Workbook | Files, Workbook, and Worksheets in VBA | Financial Formulas | Formating in VBA | General Topics in VBA | Import and Export in VBA | Information Formulas | Keyboard & Other Shortcuts in VBA | Keyboard Formula Shortcuts | Links between Worksheet and Workbooks | Links in VBA | Logical Formulas | Lookup Formulas | Mail - Send and Receive in VBA | Menus, Toolbars, Status bar in VBA | Modules, Class Modules in VBA | Other Q&A Formulas | Printing in VBA | Protecting in VBA | Summing | Text Formulas | User Forms, Input boxes in VBA | Using Loops | Working with Formulas |

Tips by Version

Microsoft Excel 97 | Microsoft Excel 2000 | Microsoft Excel 2002 | All Microsoft Excel Versions | New in Excel 2002 | New in Excel 2003 - Office 11

Website

Home | Tip of Hour | Recommended Tips | Most Viewed Tips | Tips by Version | Submit a Tip | My Tips
Microsoft Excel Tutorials | Excel Links | Write for Us | About Us | Search Results | Tip Archives | Excel Forum | Excel Forum Archives

Excel Book

Excel 97 Book | Excel 2000 Book | Excel 2002 Book | Excel XP Book | Book Store

Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com
Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation
Site Developed By: Varien