Excel

In excel what is the formula or how do i calculate the amount of minutes over 60 minutes and give the result as a number?

For example if worker a) starts at 13:30 and finishes at 14:10, no time is added.  If worker b) starts at 13:30 also but finishes at 14:50, 20 minutes need to be the result.  Start time is in one cell, end time in another cell both in hh:mm format, the result in third cell as just a number.

Thanks
TonyBarnesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TazDevil1674Commented:
If you imagine start time is in cell A2, finish time B2, type this into C2

=IF(((B2-A2)*60*24)>60,(B2-A2)*60*24,"n/a")

example
for info - the *60*24 is to conver the decimal time into whole unit minutes.

HTH
Frank WhiteCommented:
I believe something something along the lines of the following would help you:

(in cell C1, assuming A1 and B1 being "start time" and "end time" respectively, in text)
=MAX(DATEVALUE(B1)-DATEVALUE(A1)-TIME(1,0,0),0)

This gives you a serial_number value. If A1 and B1 are already and always will be in Date format using serial values, then something like =MAX(B1-A1-TIME(1,0,0),0) might also work.

The MAX() function is only to prevent negative numbers.

If you then want to convert this into minutes as a simple number, you'd use something like the following:

(in another cell, such as D1)
=HOUR(C1)*60+MINUTE(C1)

To have the whole thing happen in a single cell is possible, but will make the resulting formula less readable and slower overall because of the added duplicate calculation. Something like this might work:

=HOUR(MAX(B1-A1-TIME(1,0,0),0))*60+MINUTE(MAX(B1-A1-TIME(1,0,0),0))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TonyBarnesAuthor Commented:
Thanks, however this gives a result as 'value' in the final cell and it also calculates the total time, NOT just the extra minutes over 60, i do not want the first 60 minutes included in the answer!
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

TazDevil1674Commented:
You need to use an IF statement - like my suggestion??

You could also use Conditional Formatting on column D to make the text white on white if less than 60
TonyBarnesAuthor Commented:
Question to DaFranker, what would the formula be for anything over 30 minutes rather 60?
TazDevil1674Commented:
by: TonyBarnesPosted on 2012-03-20 at 14:18:06ID: 37742602
Question to DaFranker, what would the formula be for anything over 30 minutes rather 60?


There is no change to the formula as his formula was just giving the total.  If you want to 'hide' values you need to use an IF statement or Conditional Formatting...
Frank WhiteCommented:
Odd. My formula was tested before posting and accurately returns the substracted time in my test worksheet. Entering 13:30 in A1 and 15:35 in B1 accurately returns "65" with the last formula in my post above, which is exactly what you'd expect since 15:35 is two hours later than 13:30 and the first 60 minutes are not to be "counted". Unless I misunderstood the question/problem?

As for changing the amount of "excluded" time, you would change the TIME(1,0,0) statement. TIME takes parameters TIME(hours,minutes,seconds), so to exclude only 30 minutes instead of 60, you would replace it with TIME(0,30,0). The final result would look like this:

=HOUR(MAX(B1-A1-TIME(0,30,0),0))*60+MINUTE(MAX(B1-A1-TIME(1,0,0),0))
TonyBarnesAuthor Commented:
Yes i also had to change the *60 to *30 and the second TIME to 0,30,0 for it to work, but many thanks.
Frank WhiteCommented:
Oh, yeah. Guess I should've paid more attention to that instead of reading other stuff at the same time :P

However, the *60 multiplies hours by 60 to have a number of minutes. If you change it to 30, then each hour difference will only count for 30 minutes, including additional hours that should count for the full 60.

Just changing both TIME() statements should work, though. It does in my test sheet for all sample/example situations I can think of.

Current formula:

=HOUR(MAX(B1-A1-TIME(0,30,0),0))*60+MINUTE(MAX(B1-A1-TIME(0,30,0),0))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.