+ Reply to Thread
Results 1 to 9 of 9

Google Sheet - Difference between Work Hours and Standard Hours

  1. #1
    Registered User
    Join Date
    09-24-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    24

    Google Sheet - Difference between Work Hours and Standard Hours

    Evening,

    I am trying to build a production tracking tool that measures worked hours (man hours) versus standard hours.. I am having issues with J2 and P2 cell's calculating the incorrect difference in time.. Row 3 is working perfectly and doing exactly what I want it to. When there is not a date in H3 the if statement in J3 is using the =now() formula to register a date and P3 is handling it properly and calculating the hours between F3 and J3.

    Its only when my employee's key in the actual completion date of the part in H2 and I2 that I have issues.. I believe its due to J2 and P2. If looking at row 2, the start date is 11/14/19 at 6AM, the end date is 11/14/19 at 8AM.. The hours that should be displayed in P2 is 2.00 representing the 2 hour difference between the times.. But I cant seem to get it to work.

    Be advised that column D and column H are conditionally formatted so that you can double click the cell and add the date via calendar.. Additionally, column's E and I are drop downs for the time. I have this feature to keep the formatting the same and remove typing errors. I use power query and pull down this sheet automatically which is another reason the features must remain in these columns.


    Here is the spreadsheet in google sheets for reference..

    https://docs.google.com/spreadsheets...it?usp=sharing


    Thanks,

    Jason

  2. #2
    Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    364

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    I was modify your formula in P2 to
    Please Login or Register  to view this content.
    please re-verify the result.

    Regards.
    Last edited by menem; 11-15-2019 at 02:44 AM. Reason: Add error checking
    My English is very poor, so please be patient >_<"

  3. #3
    Registered User
    Join Date
    09-24-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    24

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    That formula would count all 7 days in a work week correct? Our work schedule is M-F.. Additionally when you remove the end date and time it no longer calculates using the =now() formula. I use J2 to combine H2 and I2..
    Last edited by jclaborn; 11-15-2019 at 07:18 AM.

  4. #4
    Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    364

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    Please try again , beware for date is SAT/SUN ^_^

    Please Login or Register  to view this content.
    Regards.

  5. #5
    Registered User
    Join Date
    09-24-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    24

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    Quote Originally Posted by menem View Post
    Please try again , beware for date is SAT/SUN ^_^

    Please Login or Register  to view this content.
    Regards.
    Thank you, its working for when a date is keyed in manually..

    I also need this formula to work when a H2 and I2 is blank.. When these cells are blank I want the formula to use the =now() formula to keep the R2 cell ticking until they hard key in a number..

  6. #6
    Registered User
    Join Date
    09-24-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    24

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    I added
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to Cell H2 and I added
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to cell I2..

    This gives me the desired result I am looking for, for this project.. But I do not want my employee's to ACTUALLY see the date in cell until they finish the project. Only see cells R2 counting down.. Once they finish the Part they will manually select the ACTUAL date they finished the part tell me if they did it under the standard hours or over..

  7. #7
    Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    364

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    I can not access your file.
    Please try by.

    Change condition of IF
    And
    Change H2 => IF( H2 = 0 , Today() , H2 )
    Change I2 => IF( AND(H2=0,I2=0) , Now() - Today() , I2)


    Will be


    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-24-2018
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    24

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    Sorry, here is the link..

    https://docs.google.com/spreadsheets...it?usp=sharing

    The formula's are working great so far. Thank you for your help..

    Last problem we are having is with P2.. Right now, since there is no date and time in F2 and G2, H2 and I2 are using the =today and =now formulas as I want them too..

    But P2 is calculating the total number of hours between the start time of 6am and the current time. It is ignoring my work schedule of 6am to 4pm.. When using the =today and =now I only want it to count the hours between 6am and 4am.. As of right now the total number of hours that should be showing in P2 would be 10.. Its currently showing 16.01, thus meaning its ignoring my work schedule in totaling the hours.


    Thanks,

    Jason
    Last edited by jclaborn; 11-16-2019 at 12:01 AM.

  9. #9
    Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    364

    Re: Google Sheet - Difference between Work Hours and Standard Hours

    I need to use helper sheet , and then change formula to

    P2
    Please Login or Register  to view this content.
    Please remind that , result is in a decimal point value not time value , so I've re-check result in P5 to verify minutes of period.

    Note: How much different minimum date of start and (current / finish date), it's need for prepare area of helper sheet
    (I've prepare for 30 days = 24 x 30 = 720 rows of formula in helper sheet).


    Regards.

+ 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