Formula for counting minute intervals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Natalie - 21 Mar 2008 00:04 GMT

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!

Reply to this Message

dan dungan - 21 Mar 2008 00:21 GMT

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.

Reply to this Message

Natalie - 25 Mar 2008 14:32 GMT

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?

> if your list of times is in column A,
> put this formula in column B:
[quoted text clipped - 3 lines]
> then select column b, Choose "Conditional Formatting", from the Format
> menu, and enter the criteria and formatting you desire.

Reply to this Message

Natalie - 27 Mar 2008 18:52 GMT

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?

> 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?
[quoted text clipped - 6 lines]
> > then select column b, Choose "Conditional Formatting", from the Format
> > menu, and enter the criteria and formatting you desire.

Reply to this Message

dan dungan - 31 Mar 2008 18:56 GMT

try this:

=(A2-A1)*24*60

Reply to this Message

Rate this thread: