• ## How much longer to work

Author
Topic
#2450667

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

Author
Replies
• #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.

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

`=40-(SUM(A5:A9)+SUM(B5:B9)/60)`

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.

`=40/24-SUM(A5:A9)`

cheers, Paul