How much longer to work

Topic

New Reply

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

Reply | Quote

Replies

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:

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

WSbuckshot

Reply | Quote

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

Reply | Quote