[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

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
0
TonyBarnes
Asked:
TonyBarnes
  • 3
  • 3
  • 3
1 Solution
 
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
0
 
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))
0
 
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!
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.

 
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
0
 
TonyBarnesAuthor Commented:
Question to DaFranker, what would the formula be for anything over 30 minutes rather 60?
0
 
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...
0
 
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))
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.
0
 
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))
0

Featured Post

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.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now