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

  • 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. – marikamitsos Jan 7 at 20:24
  • @marikamitsos Please see this sheet here.[link] (docs.google.com/spreadsheets/d/…) – Stan Jan 7 at 20:58
  • Please check the answer and your sheet – marikamitsos Jan 7 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. – marikamitsos Jan 7 at 23:00
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

|improve this answer|||||
  • Thank you soo much!!! works perfectly. – Stan Jan 8 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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