• How much longer to work


    I’m using Excel 365. It’s set up where I enter my hours in one column and minutes in another. I would like a formula with will tell me I have to work (this many more hours/minutes to get 40 hours). Our punch time is based on 6 minute in-between-8:04 to 8:09 is rounded to 8:06

    Viewing 1 reply thread
    • #2450784

      I always enter times as hh:mm, then Excel stores and formats them as time. Or use zeddy’s “no colon” method.

      To work out total time you need to work on the hours and minutes separately.

      Sum minutes then divide by 60.
      Sum hours and add the result of the minutes.
      Subtract the answer from 40.

      Are you expecting the time range to change? (I assume you have a growing list with dates and times?)

      Based on your screenshot:


      Now you need to convert that figure to hours and minutes.

      Hours: =INT(40-(SUM(A5:A9)+SUM(B5:B9)/60))
      Minutes: =(40-(SUM(A5:A9)+SUM(B5:B9)/60)-INT(40-(SUM(A5:A9)+SUM(B5:B9)/60)))*60

      cheers, Paul

      1 user thanked author for this post.
    • #2450788

      It would have been much easier if you had stored the times as TIME in the first place.


      cheers, Paul

    Viewing 1 reply thread
    Reply To: How much longer to work

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: