Solved

Excel

Posted on 2012-03-20
9
375 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
  • 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:
DaFranker earned 500 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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:DaFranker
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:DaFranker
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Microsoft Office 2010 is Microsoft's latest edition to their office productivity collection. It "promises to include rich and powerful new ways to deliver your best work". So to start off, I have to say that I'm slightly disappointed... That bei…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now