2

I want to count # of hours worked by subtracting the Start & end time in 1 cell (as per my comp template) ( 9am - 6pm) .( No I can't alter the template to put start & end in different cells/columns)

I've used this formula now - HOURS()-HOURS(). The problem is it gives me the count only in hours - so 9:00:00 -17:00 gives the result as 8.00 hours but if there is half an hour extra it doesn't show as 8.50

Can you help me understand how I can achieve the right result up-to the minute mark? Thanks

4
  • Welcome. "No I can't alter the template to put start & end in different cells/columns" also "...if there is half an hour extra it doesn't show as 8.50". Please share a test sheet with dummy data and expected results. Jan 7, 2020 at 20:24
  • @marikamitsos Please see this sheet here.[link] (docs.google.com/spreadsheets/d/…)
    – Stan
    Jan 7, 2020 at 20:58
  • Please check the answer and your sheet Jan 7, 2020 at 22:54
  • Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Jan 7, 2020 at 23:00

1 Answer 1

1

Since you use a single cell to input both your data and formula, you should change your "formula" from =HOUR("17:30:00") - Hour("9:00:00") to simply

=("17:30:00" - "9:00:00")*24
The above formula will give you the desired result 8.5.

IMPORTANT. Make sure you format your cells as Number
( by going to: Format > Number > Number)

enter image description here

1
  • Thank you soo much!!! works perfectly.
    – Stan
    Jan 8, 2020 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.