Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel

Posted on 2012-03-20
9
Medium Priority
?
386 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

618 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