+ Reply to Thread
Results 1 to 15 of 15

Formula to calculate hours worked

  1. #1
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Formula to calculate hours worked

    I am trying to create a work spreadsheet that I can input the time our shift starts and ends and a formula to calculate how many hours each person has worked each day. For example:

    Start work @ 8:15am
    Finish work @ 4.30pm
    Total hours worked 8.25 hours worked

    The total hours worked has to be worked out so that half an hour is shown as 0.50, fifteen minutes shown as 0.25 etc.

    The shifts and hours worked change from day-to-day and week-to-week, so I need to be able to just input the start and finish times and let the spreadsheet work out how many hours have been worked.

    Any help would be greatly appreciated.

    Michelle x

  2. #2
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Formula to calculate hours worked

    Simple take the difference of two cell and multilply by 24. e.g. cell B2 contains starting time and cell C2 contains end time. then in cell two type the formula =(C2-B2)*24

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to calculate hours worked

    =24*(B1-A1) - formatted as GENERAL will do it
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to calculate hours worked

    Faraz beat me to it!!

  5. #5
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula to calculate hours worked

    Ok I'm gonna sound really dumb here (I am completely useless with Excel though so be gentle!) but I've put that formula in the cell and the figure I'm getting as the hours worked is 195.6 instead of 8.25

    x

  6. #6
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Formula to calculate hours worked

    Can you share the worksheet or tell the values that you have typed?

    Try to type time like 4:30:00 PM.

  7. #7
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula to calculate hours worked

    That's worked I was typing the time in as 08:15 but when I typed it in as 08:15:00 it has worked out perfectly. Thank you so much for your help, it's been wrecking my head for ages! Next time I'll just come straight to you guys.

    Much love x

  8. #8
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Formula to calculate hours worked

    If your problem is solved please mark it as solved and Don't forget to add reputation at the bottom left corner of message.

  9. #9
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula to calculate hours worked

    Actually there is one more thing...

    How can I subtract the break time from the hours worked? I've got =SUM(C2:C4)*24 which works out perfectly the hours worked (although the start time is in C2 and the finish time is in C3), but if I wanted to subtract say a one hour break from that, what should the new formula be? The break time will be in C4 x

  10. #10
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Formula to calculate hours worked

    =(c3-c2)*24 -1 for fixed one hour break
    =(c3-c2)*24-(c4*24) for different break time, based on the value in cell c4.
    Last edited by Faraz.Ahmed; 05-13-2014 at 05:51 PM.

  11. #11
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula to calculate hours worked

    This is what I am doing, the column with the actual hours in is C and start time is on line 2:

    Start 08:15:00
    Finish 16:30:00
    Break 1.00
    Hours (what is the formula that goes here?)

    The break times vary depending on how many hours have been worked that day, so I need to input the amount of break time manually. Also, should the whole lot be in GENERAL format or something else? x

  12. #12
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Formula to calculate hours worked

    =(c3-c2)*24-(c4*24)

    Cell in which answer is required should be in General.

  13. #13
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula to calculate hours worked

    Hmmm that's not working. It's ok, I'll just leave it at the hours worked and manually subtract the break times, I feel like I'm being a bit of a nuisance as I don't know anything about Excel.

    Thank you for your help Faraz, I appreciate it. I shall mark the thread as solved and click the Add Reputation thingy in a moment.

    x

  14. #14
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Formula to calculate hours worked

    Apologies Faraz, it IS working, it was just me putting the time format in wrong AGAIN! x

  15. #15
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Formula to calculate hours worked

    Pleasure, LilMissM

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula To Calculate Hours Worked In a Day
    By SilverFox in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-15-2013, 05:21 AM
  2. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  3. Replies: 2
    Last Post: 02-11-2013, 02:26 PM
  4. Formula to calculate hours worked
    By mikeburg in forum Excel General
    Replies: 6
    Last Post: 05-06-2008, 04:02 PM
  5. Need formula to calculate hours worked
    By BankC in forum Excel General
    Replies: 12
    Last Post: 01-31-2006, 01:38 PM

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