Dividing week up in to 6hr blocks using date and time data

[jlaff]

Hi,

I have a large (11k rows) data set for a health research project. Each event has a date and time value. Please see image attached.

I want to put these in to 28 groups across the week (each bucket containing a quarter of a day) - ie Group 1: Sun 00:00-05:59, Group 2 Sun 06:00-11:59, .... Group 23 Sat 12:00-17:59, Group 24 Sat 18:00-23:59. Is there an excel command that I can use for this?

Apologies my excel knowledge is minimal but there is a lot of work to manually do each one. I have merged them in to one column and have also generated a day of the week column as well if that helps!

Many thanks,

Jack

 

[FormR]

Hi, something like this maybe..

Book3
	E	F	G
1	Date	Time	Group
2	26/12/2012	13:23	Wed 12:00-17:59
3	27/12/2012	21:30	Thu 18:00-23:59
4	29/12/2012	10:48	Sat 06:00-11:59
5	30/12/2012	17:30	Sun 12:00-17:59
6	30/12/2012	14:49	Sun 12:00-17:59
7	30/12/2012	02:12	Sun 00:00-05:59
8	30/12/2012	20:49	Sun 18:00-23:59
9	31/12/2012	01:08	Mon 00:00-05:59
10	31/12/2012	06:00	Mon 06:00-11:59
11	31/12/2012	05:59	Mon 00:00-05:59
12	30/01/2025	18:00	Thu 18:00-23:59
13	31/01/2025	17:59	Fri 12:00-17:59
Sheet1

Cell Formulas
Range	Formula
G2:G13	G2	=LET(s,FLOOR(F2,"06:00"),e,s+"05:59",TEXT(E2,"ddd")&" "&TEXT(s,"hh:mm")&"-"&TEXT(e,"hh:mm"))

 

[jlaff]

Thanks so much for your help!