+ Reply to Thread
Results 1 to 3 of 3

Date and time calculation excluding breaks, weekends for a time period

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2016
    Posts
    76

    Date and time calculation excluding breaks, weekends for a time period

    Hello all,

    I am truly having a moment and struggling with this.

    I have a date/time in this format: 31/01/2017 09:00 in C5 and trying to get a desired finish date in the same format in C6.

    This is based on the number of hours to complete the task in a number format in F5. However I want to take in account weekends, breaks and meetings in order to get an accurate completion time and date. A28-C33 contains the below:

    08:00 Start
    08:00 08:15 Meeting
    10:00 10:15 Morning Break
    12:45 13:15 Lunch
    16:15 16:30 Meeting
    16:30 Finish

    Basically Monday-Friday start at 08:15 and finish 16:15 with a 15min break at 10am and 30min lunch at 12:45.

    For example
    C5-30/01/2017 14:00 F5-4 Therefore C6 should be 31/01/2017 10:00

    Any assistance would be greatly appreciated, my mind is getting mixed up.

    Peter
    Attached Files Attached Files
    Last edited by Pubhub; 02-06-2017 at 10:30 AM. Reason: Attachment Added

  2. #2
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office XP to Office 2016
    Posts
    1,696

    Re: Date and time calculation excluding breaks, weekends for a time period

    Peter you explained well.But it would be better for the others to look at a readymade workbook (with desensitized data) so that the possible solutions could be applied quickly without out typing the data again.
    Teach me Excel VBA

  3. #3
    Registered User
    Join Date
    05-29-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2016
    Posts
    76

    Re: Date and time calculation excluding breaks, weekends for a time period

    @ImranBhatti I was just in the process of adding the attached blank document to assist.

    The one additional which I would end up doing manually is overtime. Sometimes a 07:00 start or a 17:30 finish. But this would give a basis and would drastically reduce manual calculation.

    Many thanks on your response.

    Peter
    Attached Files Attached Files

+ 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. Time between 2 times excluding weekends
    By jezza25 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-19-2014, 02:19 AM
  2. Help with calculating time, excluding weekends
    By ninamadeleine in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2013, 03:37 AM
  3. Time Between Two Dates Excluding weekends
    By RjMaJay in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2012, 09:34 AM
  4. Date/Time Calculation excluding weekends and holiday list not working
    By mikeyk80 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-31-2012, 10:39 AM
  5. Replies: 0
    Last Post: 04-16-2012, 05:47 AM
  6. Replies: 0
    Last Post: 04-16-2012, 04:31 AM
  7. Replies: 3
    Last Post: 12-23-2010, 04:46 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