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

Originally Posted by menem

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..

I added

Formula:

Please Login or Register  to view this content.

to Cell H2 and I added

Formula:

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..