» Creating a Timesheet

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

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.

Thanks you for every thing. Now I can keep in trak on my time card...

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

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.

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?

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.

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!

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.

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.

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.

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?

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 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]

The topic "My favorite timesheet template" was my first post. If you see /B instead of Bold print, please ignore it.
Thanks,
Gary Brown

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=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

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.

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?

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?

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

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

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

[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.

[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

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

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

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

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.

[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]

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

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

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