+ Reply to Thread
Results 1 to 2 of 2

Excluding weekend hours from time

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    68

    Excluding weekend hours from time

    Afternoon folks

    I have a table of data from work "Data Table" (cutting patterns used in a sawmill) and certain patterns have very long running hours due to error reports. The pattern has been "loaded" over the weekend and thinks it has run for several days, in reality it has only run a few hours driving the "m3/hour" right down

    The sawmill stops running production every Friday at 3pm and start back up on Monday morning at 6am however it has done overtime on following

    Saturday 19/07/2014 06:00 - 14:00 8 hours
    Saturday 02/08/2014 06:00 - 14:00 8 hours
    Saturday 09/08/2014 06:00 - 14:00 8 hours
    Saturday 16/08/2014 06:00 - 14:00 8 hours
    Saturday 23/08/2014 06:00 - 14:00 8 hours

    Is it possible to remove all hours between the Fri 3pm to Monday 6am except for the times above from all patterns to get a true m3/hour value for each pattern by replacing the hours and minutes columns in the table with some sort of code to do this calculation?

    I have attached my sheet for anyone who wants to help

    Thanks in advance

    Jonny
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Excluding weekend hours from time

    Hi Jonny,

    I started this and somehow (i think) I made it overcomplicated,
    But as there are no other answers - see mine.
    Macro is rather long (algorithm is rather strightforward - my coding was far from perfect and made it so long) but I used made some comments inside, to make it easier to understand.

    As for results: note especially:
    - row 15 (changed data to simulate special case of "start" Monday before real starting hour)
    - row 85 - here start is in Sat previous week
    - row 216 and some motre with green background - start in overtime but before 6am corected
    Also have a look on Overtime sheet - 3 columns format has to be followed - date start_time end_time

    And as I wrote in comments - only hours are calculated (but located in T at the moment to easily spot difference), other columns you shall correct yourself (of course, this macro).


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

+ 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. Need help Calculating Elasped time excluding non business hours.
    By hellpme in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2013, 03:52 PM
  2. [SOLVED] Calculate hours with predefined Working Hours and Weekend Hours
    By garciapliz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-05-2013, 11:17 AM
  3. Time Tracking, excluding nonworking hours.
    By hayduck in forum Excel General
    Replies: 8
    Last Post: 07-05-2012, 11:57 AM
  4. Time between timestamps excluding certain hours
    By Silaswulff in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-01-2011, 06:08 PM
  5. Replies: 0
    Last Post: 04-07-2011, 01:46 AM

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