Solved

group times in excel

Posted on 2014-12-22
9
76 Views
Last Modified: 2015-02-11
I have imported data that i would like to group the times into one hour time slots all times betwee 0000 and 0059 would be one etc.etc.
thanks
Book1times.xlsx
0
Comment
Question by:Svgmassive
9 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 250 total points
ID: 40513410
Is this what you are looking for..??

Saurabh...
Book1times.xlsx
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 40513413
Please provide more information on how you wish to see the grouped output.

It's easy enough to sort this, or better yet, create a PivotTable and group the data by Hours.  However, it's better that you describe you needs in more detail.

See the attached example file.

Regards,
-Glenn
EE-Book1times-2.xlsx
0
 

Author Comment

by:Svgmassive
ID: 40513879
glen i am looking to assign a number to each times in an adjacent column example 0000 to 0059 would be 1 etc
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40513911
So, you want a new column adjacent to the first that assigns an hour number to each time value shown?  For example, any time value in column A between 00:00 and 00:59 inclusive would return 1;  between 01:00 and 01:59 would return 2, and so on.  All that's needed to know is what value do you wish for times between 23:00 and 23:59:   Zero (0) or Twenty-four (24)?

Insert one of these formulas in cell B2 and copy down:

For 23:00-23:59 to return zero:
=MOD(ROUNDUP(A16*24+TIME(0,1,0),0),24)

For 23:00-23:59 to return 24:
=ROUNDUP(A2*24+TIME(0,1,0),0)

Regards,
-Glenn
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 40516331
You can even do this...

=CEILING(A1,TIME(1,,))

This will convert any time value to upper side of time..

and if you looking to convert it down then use this...

=Floor(A1,TIME(1,,))

Saurabh..
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40516913
Couple of follow-up notes:

I didn't provide the correct formulas, given your original set of data.  If your data begins in row 1, then you would want to insert one of these formulas in an adjacent column, also in row 1:

For 23:00-23:59 to return 24:
=ROUNDUP(A1*24+TIME(0,1,0),0)

For 23:00-23:59 to return zero:
=MOD(ROUNDUP(A1*24+TIME(0,1,0),0),24)

Secondly, saurabh726's formulas return time values rounded up (CEILING) or down (FLOOR), but not whole number values that you appear to be requesting.  For example, if the time value is 12:47 (in cell A1), the formula
=CEILING(A1,TIME(1,,))
returns 0.5416667, which is the internal time value for 13:00 or 1:00 PM, but not 13.

Additionally, time values between 23:00 and 24:00 all result in a whole value of 1.000, which returns a time value of 0:00.

To use the CEILING function to return a whole number value, it is analgous with the ROUNDUP and could be used like so:

For 23:00-23:59 to return 24:
=CEILING(A1*24+TIME(0,1,0),1)

For 23:00-23:59 to return zero:
=MOD(CEILING(A1*24+TIME(0,1,0),1),24)

I've attached a modified file to demonstrate all this.

Regards,
-Glenn
EE-Book1times-2.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40552550
Did you have any additional questions about the solutions provided?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40602761
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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