Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

group times in excel

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
Svgmassive
Asked:
Svgmassive
2 Solutions
 
Saurabh Singh TeotiaCommented:
Is this what you are looking for..??

Saurabh...
Book1times.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
SvgmassiveAuthor Commented:
glen i am looking to assign a number to each times in an adjacent column example 0000 to 0059 would be 1 etc
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Glenn RayExcel VBA DeveloperCommented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
Did you have any additional questions about the solutions provided?
0
 
Martin LissRetired ProgrammerCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now