+ Reply to Thread
Results 1 to 9 of 9

Time Tracking, excluding nonworking hours.

  1. #1
    Registered User
    Join Date
    06-08-2012
    Location
    Arizona
    MS-Off Ver
    Excel 2003
    Posts
    4

    Time Tracking, excluding nonworking hours.

    I am doing some value stream mapping at work and I'm trying to track how long it takes for certain tasks to complete.

    At first I was just entering a start time/date in A1 and end time/date in B1, then =B1-A1 in C1.
    This works fine, but some things sit over night and I don't want the time included where nobody is at work.

    For example 1st shift is from 6:00am-2:00pm and 2nd shift is from 2:00pm-12am, nobody works from 12:00am to 6am. I want to enter a Start time at 8:00pm and End time at 8:00am and get excell to exclude the time between 12:00am and 6:00am, so the value I get is 6 hours instead of 12 hours.

    I hope I explained this well enough, any help would be greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Time Tracking, excluding nonworking hours.

    Did you try just subtracting out the 6 hours if the time goes past 12 am?

  3. #3
    Registered User
    Join Date
    06-08-2012
    Location
    Arizona
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Time Tracking, excluding nonworking hours.

    I guess that is what I want to do, I just dont know how to do it. Sorry, I'm really a novice exel user.

  4. #4
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Time Tracking, excluding nonworking hours.

    Hayduck,

    Can you post your workbook, Reply-->Go Advanced-->Manage Attachments. Select your file and post it so that I can look at it and make the correct changes. Also highlight or somehow indicate which cells you are trying to change, where your subtractions are taking place.

  5. #5
    Registered User
    Join Date
    06-08-2012
    Location
    Arizona
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Time Tracking, excluding nonworking hours.

    Here you go.

    thanksyou
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Time Tracking, excluding nonworking hours.

    Here is your workbook with the corrected formula. I used this formula:

    =IF(DATE(YEAR(E3),MONTH(E3),DAY(E3))>DATE(YEAR(D3),MONTH(D3),DAY(D3)),(E3-D3)-(5.75/24),E3-D3)

    Basically it is saying if the end date is greater than the start then subtract the number of hours and from that subtract 5.75/24 because that is the time between 12:15 and 6 in a day, so basically i am saying that is 5.75 hours out of the total 24 hours. As long as you don't have this formula stretch over more than 2 days you will be fine, otherwise you will need to add a few more things into this formula to see how many days it stretches over and then multiply that by the value of 5.75/24. Please let me know if this helped you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-08-2012
    Location
    Arizona
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Time Tracking, excluding nonworking hours.

    That is exactly what I was looking for, but somtimes it will stretch for more than 2 days. How would that formula for more than 2days look? As an after thought I realized that somtimes tasks will carry over the weekend, is it possible to account for weekends as well?

  8. #8
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Time Tracking, excluding nonworking hours.

    Try this formula in there and see if it works, this should work for more than one day. Although i am not sure what you mean by extending over the weekend. I am guessing i could figure out a formula for it with a little more information. Are you trying to exclude all 48 hours of the weekend? i guess what i am trying to say is how are you differentiating a weekend from a set of weekdays?

    =IF(DATE(YEAR(E12),MONTH(E12),DAY(E12))>DATE(YEAR(D12),MONTH(D12),DAY(D12)),(E12-D12)-((DATE(YEAR(E12),MONTH(E12),DAY(E12))-DATE(YEAR(D12),MONTH(D12),DAY(D12)))*(5.75/24)),E12-D12)
    Last edited by amotto11; 06-11-2012 at 06:01 PM.

  9. #9
    Registered User
    Join Date
    06-29-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Time Tracking, excluding nonworking hours.

    Quote Originally Posted by hayduck View Post
    I am doing some value stream mapping at work and I'm trying to track how long it takes for certain tasks to complete.

    At first I was just entering a start time/date in A1 and end time/date in B1, then =B1-A1 in C1.
    This works fine, but some things sit over night and I don't want the time included where nobody is at work.

    For example 1st shift is from 6:00am-2:00pm and 2nd shift is from 2:00pm-12am, nobody works from 12:00am to 6am. I want to enter a Start time at 8:00pm and End time at 8:00am and get excell to exclude the time between 12:00am and 6:00am, so the value I get is 6 hours instead of 12 hours.

    I hope I explained this well enough, any help would be greatly appreciated.
    Hi Hayduck,

    Most of us do mistakes in hour calculations and miss out minute calculations too. I tried making my own timesheet and got stuck with some issues in it. So, I changed my mind and started using third party softwares, cloud based for tracking my time and project. I am currently using Replicon Project tracking software for tracking time and project bills.

    Hope it helps...

+ Reply to 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