Formula for counting minute intervals

N

Natalie

I need help creating a formula! I have to keep track of my time and it goes
by minutes. If there is no activity after 7 minutes, the system assumes I've
taken a break. From the system I'm able to copy my time and paste it into an
excel sheet.
For instance, these are my times this morning:
10:29:00 AM
10:31:00 AM
10:31:00 AM
10:32:00 AM
10:32:00 AM
10:32:00 AM
10:37:00 AM
10:37:00 AM
10:40:00 AM
10:52:00 AM
10:52:00 AM
11:16:00 AM
11:16:00 AM
In the next column, I want to have a formula that counts the minutes that
have passed and if there is a break (>7 minute interval) mark the cell red or
yellow.
Thank you in advance for any assistance!
 
D

dan dungan

if your list of times is in column A,
put this formula in column B:

=IF(ISERROR(MINUTE(A9-A8)),"",MINUTE(A9-A8))

then select column b, Choose "Conditional Formatting", from the Format
menu, and enter the criteria and formatting you desire.
 
N

Natalie

Thanks, Dan! That does work out well. One quick addition: How do I get it to
leave a blank instead of putting a 0 when the time has not changed?
 
N

Natalie

I figured out how to put the blank instead of a 0 (through Tools/Options/View
- Zere Values).
I did notice that when there is a different hour, that the formula will just
look at the minute section. For instance:
12:08:00 PM
12:12:00 PM
12:12:00 PM
12:15:00 PM
5:19:00 PM
5:19:00 PM
5:25:00 PM
Between 12:15 and 5:19, I get 4 minutes. How do I get it to tell me 5 hours
4 minutes or 304 minutes?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extract the 2 Lowest % 3
Break Calculation Macro 1
Time and Minute formulas 2
INDEX MATCH SMALL 13
Vlookup/Hlookup confusion 1
Averaging Time 8
Need help with a Formula 4
Compute minutes between hh:mm 2

Top