?
Solved

Excel

Posted on 2012-03-20
9
Medium Priority
?
385 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:TonyBarnes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 9

Expert Comment

by:TazDevil1674
ID: 37742312
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
0
 
LVL 3

Accepted Solution

by:
Frank White earned 2000 total points
ID: 37742359
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))
0
 

Author Comment

by:TonyBarnes
ID: 37742400
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!
0
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
LVL 9

Expert Comment

by:TazDevil1674
ID: 37742440
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
0
 

Author Comment

by:TonyBarnes
ID: 37742602
Question to DaFranker, what would the formula be for anything over 30 minutes rather 60?
0
 
LVL 9

Expert Comment

by:TazDevil1674
ID: 37742665
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...
0
 
LVL 3

Expert Comment

by:Frank White
ID: 37742749
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))
0
 

Author Comment

by:TonyBarnes
ID: 37742999
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.
0
 
LVL 3

Expert Comment

by:Frank White
ID: 37743019
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))
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question