Closed Thread
Results 1 to 34 of 34

Creating a Timesheet

  1. #1
    Tita
    Guest

    Calculating time after midnight.

    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.

  2. #2
    Ben
    Guest

    How to reverse the calculation|

    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?
    [email protected]


    Thanks,
    Ben

  3. #3
    RajinCajun
    Guest

    Adding hours to dates

    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

  4. #4
    Kate
    Guest

    search a particular item thoughout worksheet

    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

  5. #5
    AMOC
    Guest
    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?

  6. #6
    Jennifer
    Guest

    Creating a Timesheet

    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.

  7. #7
    D. Mckeever
    Guest

    timestamp

    [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

  8. #8
    Chuck
    Guest

    Converting

    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?

  9. #9
    Lee Ann
    Guest

    Help with after midnight timesheet entries

    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.


  10. #10
    Lee Ann
    Guest

    Help with after midnight timesheet entries

    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.


  11. #11
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Creating a Timesheet

    Problem:

    Creating a timesheet calculating the number of hours worked each day, and their total.
    Solution:

    Create 3 columns containing the following data: Date, Time In, Time Out.

    In the 4th column (Number of Hours), enter this formula:
    =(C2-B2+(C2

    To sum the total of hours worked, use this formula:
    =SUM(D2:D7)

    Example:

    Date________Time In_____Time Out
    4/11/2005___09:00________17:00
    4/12/2005___10:30________19:00
    4/13/2005___08:30________17:30
    4/14/2005___11:00________16:00
    4/15/2005___12:00________0:30
    4/16/2005___09:30________16:45



    Number of Hours
    8.00
    8.50
    9.00
    5.00
    12.50
    7.25

    Total Hours 50.25

  12. #12
    Martin Borcher
    Guest

    How do you make a total hour calculation?


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

    ASP

    Martin Borcher

  13. #13
    Registered User
    Join Date
    06-23-2005
    Posts
    1

    Display sum of hours when > 24h

    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.

  14. #14
    michael A.
    Guest

    Good examples to create time sheet

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



  15. #15
    Amanda
    Guest

    Calculating wages on weekly hours

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

  16. #16
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: Amanda

    Hi Amanda,

    Quote Originally Posted by 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 protected]
    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.

  17. #17
    Seano
    Guest

    How to take break times into account?

    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?

  18. #18
    Registered User
    Join Date
    08-09-2005
    Location
    Philippines
    Posts
    1
    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!

  19. #19
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: Seano

    Hi Seano,

    Quote Originally Posted by 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?
    Just do something like:

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

    HTH,

    Alan.
    To help us help you, try to do the following:

    1) Be precise about what you want to do, and provide a sample of your data / inputs - exactly as they are.

    2) State the formula(e) / code that you have tried. People are happy to help , but if you haven't even given it a go, you are less likely to get help, or the help you get will be very basic.

    3) State the results you are getting from your formula(e) / code already.

    4) State the outputs that you *want* to be getting.

  20. #20
    Registered User
    Join Date
    08-09-2005
    Posts
    5

    Different Time Sheet

    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.

  21. #21
    Registered User
    Join Date
    11-04-2005
    Location
    Connecticut, USA
    Posts
    5

    Talking My favorite timesheet template

    My favorite timesheet has 9 columns A:I.

    Col A: Wk# =WEEKNUM(C3)

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

    Col C: Date =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: Start [direct input] 7:48:00 AM

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

    Col F: Lunch [direct input] .75

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

    Col H: *** Hrs/Wk =IF(A3<>A4,SUMIF(A:A,A3,G:G),"")

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

    This format rounds to the nearest 15 minutes [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 takes working past midnight into account.
    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
    gary_brown@ge_NOSPAM.com
    Last edited by Gary L Brown; 11-11-2005 at 10:14 AM.

  22. #22
    Registered User
    Join Date
    11-04-2005
    Location
    Connecticut, USA
    Posts
    5

    Unhappy My favorite timesheet template

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

  23. #23
    Registered User
    Join Date
    03-10-2005
    Posts
    46

    timestamp

    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?

  24. #24
    Registered User
    Join Date
    08-14-2003
    Location
    New Zealand
    Posts
    41

    Reply: owl527

    Hi owl527,

    Quote Originally Posted by 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?
    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.

  25. #25
    Registered User
    Join Date
    01-03-2006
    Posts
    7
    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?

  26. #26
    Registered User
    Join Date
    03-10-2006
    Posts
    1

    Unhappy Help with Formula for OT on 40 hrs workweek

    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

  27. #27
    Registered User
    Join Date
    11-04-2005
    Location
    Connecticut, USA
    Posts
    5

    timesheet

    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

  28. #28
    Registered User
    Join Date
    04-06-2006
    Posts
    1

    Post Spreadsheet with actual hourly time

    Quote Originally Posted by 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?
    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.

  29. #29
    Registered User
    Join Date
    04-13-2006
    Posts
    6
    Quote Originally Posted by 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.


    =(b1-a1)*24 right click format click number

  30. #30
    Registered User
    Join Date
    06-06-2006
    Posts
    1

    Requesting help on time sheet to round up to next hour

    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

  31. #31
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Quote Originally Posted by 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
    this formula gets the total hours worked and then rounds the total to the nearest 15 min

    Please Login or Register  to view this content.

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

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

    Please Login or Register  to view this content.

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

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

    Please Login or Register  to view this content.

  32. #32
    Registered User
    Join Date
    03-26-2007
    Posts
    1

    conversion of unit of 2sec to mins and hours

    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 protected]
    Thanks
    Steve

  33. #33
    Registered User
    Join Date
    05-03-2007
    Posts
    1

    Overtime Calculating

    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

  34. #34
    Registered User
    Join Date
    05-14-2007
    Posts
    1

    Calculating Overtime and Double Time Hours based on time of day

    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

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1