+ Reply to Thread
Results 1 to 10 of 10

Calculate time in Excel 2010

  1. #1
    Registered User
    Join Date
    08-23-2017
    Location
    Tuzla
    MS-Off Ver
    2016
    Posts
    10

    Calculate time in Excel 2010

    I'm working on a kind of timesheet for my company, the problem is we have three shifts and so three different starting/ending hours.
    First shift starts at 7:00 AM, second at 3:30 PM and third at 12:00 AM.

    The timesheet I would like to create is that you just write down when someone started to work and when he ended his work, then Excel should automatically calculate how many hours someone worked, if someone worked overtime (worked over 8 hours a day) and if someone worked at night (from 10:00 PM to 6:00 AM). If someoneworked during night Excel should write down how many hours. And if someone worked overtime AND during the night Excel should write down how many hours were worked overtime and how many hours during the night seperately.

    I managed to write formulas to calculate how many hours someone worked and how many hours were worked overtime.
    My problem is to calculate the work done at night and to write overtime and night work seperately.

    Here's a link with a picture for easier understanding:

    http://pokit.org/get/img/0636feded4b...bad90e833f.jpg

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Calculate time in Excel 2010

    Hi,

    Welcome to the Forum.

    Can you attach a sample file in excel format instead of image with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

    See the following URL for help on how to upload a file.
    http://www.excelforum.com/members/da...ch-a-file.html

  3. #3
    Registered User
    Join Date
    08-23-2017
    Location
    Tuzla
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate time in Excel 2010

    Thanks for the welcome. Here's the file with the important information.
    Attached Files Attached Files

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Calculate time in Excel 2010

    Hi,

    Try the following formulae:

    In D10:
    =IF(OR(D7="",D9=""),"",IF(D9>D7,D9-D7,1-D7+MOD(D9,1)))

    In D11:
    =IF(OR(D7="",D9=""),"",IF(AND(D7>=6/24,D7<=22/24,D9>=6/24,D9<=22/24,D7<D9),0,IF(D7>D9,1-MAX(D7,D14)+MIN(D9,D15),IF(D7=0,MIN(D9,D15)-D7,MAX(D15,D9)-MAX(D7,D14)))))

    In D12:
    =IF(OR(D7="",D9=""),"",ABS(IF(D9>D7,D9-D7,1-D7+MOD(D9,1))-8/24))

    drag these across.

    see the attached file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-23-2017
    Location
    Tuzla
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate time in Excel 2010

    That could do the trick, but there is one more problem. If a worker hasn't worked a day or so, I can't write that he worked from 0:00 to 0:00 because Excel calculates 16 hours of overtime nor can I leave the hours unwritten because then the cell "Total hours" would say "#VALUE!"

  6. #6
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Calculate time in Excel 2010

    I've already covered that in the formula that I posted in post #4. If a worker is absent on any day, you can leave the cells blank, the result won't be affected.

    Please go through the excel file that I had uploaded and let me know if you face any further issues.

  7. #7
    Registered User
    Join Date
    08-23-2017
    Location
    Tuzla
    MS-Off Ver
    2016
    Posts
    10
    Quote Originally Posted by cbatrody View Post
    I've already covered that in the formula that I posted in post #4. If a worker is absent on any day, you can leave the cells blank, the result won't be affected.

    Please go through the excel file that I had uploaded and let me know if you face any further issues.

    Okay will do by Friday and will let you know. Thanks for helping me out!

  8. #8
    Registered User
    Join Date
    08-23-2017
    Location
    Tuzla
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate time in Excel 2010

    Hi cbatrody, your work is great! Is there just a way to include the Pause? The "Total daily working hours" minus the cell "Pause" gives me the "###" and "#VALUE!" in the cell "Total hours" , "Night shift (hours)" and "Overtime (hours)" when the other cells are left empty (in case someone doesn't come to work).
    Attached Files Attached Files

  9. #9
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Calculate time in Excel 2010

    Hi longchamp,

    See the attached file.

    I've modified the formula in D10 as following:

    =IF(OR(D7="",D9=""),"",IF(D9>D7,D9-D7,1-D7+MOD(D9,1))-D8)


    In D12:
    =IF(OR(D7="",D9=""),"",ABS(IF(D9>D7,D9-D7,1-D7+MOD(D9,1))-8/24)-D8)
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-23-2017
    Location
    Tuzla
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate time in Excel 2010

    That did the job, thank you very much!

+ 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] Excel 2010 calculate time used with stop /start at given times
    By Roger1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2015, 10:59 AM
  2. Replies: 0
    Last Post: 06-10-2014, 07:03 PM
  3. Excel 2010 time start and time end defined by slicers
    By Shmulik_r in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-21-2014, 08:59 AM
  4. [SOLVED] How to calculate age in Excel 2010
    By car24winsagain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-01-2014, 10:31 AM
  5. Replies: 31
    Last Post: 09-06-2012, 09:42 AM
  6. Excel 2010 Auto Calculate Not functioning
    By yimtiaz in forum Excel General
    Replies: 3
    Last Post: 05-10-2012, 05:47 AM
  7. Removing time from date/time data - Excel 2010
    By Huzzah in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2011, 12:03 PM

Tags for this Thread

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