Excel Grouping Times - Looking for an easy way 3

[kcoleman26]

I am constantly getting spreadsheets with arrival times on them that my boss wants broken down into 30min intervels. I have been doing this manually and was wondering if a formula could be used to do this quickly.

I get the data
8:10 AM
8:20 AM
8:35 AM
8:53 AM

I would like to create another column that would display the data like:
8:00 AM
8:30 AM
8:30 AM
9:00 AM
using 7:45 - 8:14 = 8:00 AM
8:15 - 8:44 = 8:30 AM
8:45 - 9:14 = 9:00 AM
and so on.

kc

 

[SkipVought]

Hi,

If your time is in A2, then in B2...
[tt]
B2: =INT(A2*24*60/15)*15/24/60
[/tt]


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[kcoleman26]

Sweet, but what do I change to make it 30 min intervels instead of 15min intervels.

kc

 

[SkipVought]

use 30 instead of 15

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

Here's the way that I would do it.

1. Create a named range cell, named Interval

2. modify the formula...
[tt]
=INT(A2*24*60/Interval)*Interval/24/60

[/tt]
Then, all you have to do, is change the value in Interval, and all your formulas, that you have copied down thru the rows of data, with recalculate accordingly.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

FYI

faq68-5827

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[ck1999]

Skip,

I tested your formula and it seems to alway round down to the last quarter hour it passed.

ck1999

 

[AnotherHiggins]

Here's another approach:

=FLOOR(A2-INT(A2),"0:30:00")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I just noticed that in your original post you wanted to round to the nearest quarter-hour, and both Skip's and my suggestions take it down to the next-lowest quarter-hour.

This should do what you asked:
[tab][COLOR=blue white]=MRound(A1, Time(0,30,0))[/color]

you could always use the named range as suggested above instead of the "30".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Sorry, I did not notice the 15 min offset...
[tt]
=INT((A2+15/24/60)*24*60/Interval)*Interval/24/60
[/tt]


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

John,

Yours: with finesse and panache.

Mine: with a hammer and chisel. ;-)

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

It's a rare day indeed that I best you, my friend.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.