How to sort a single column of dated & timed hourly data into Weekday & Weekend profiles.

[DalkeyJMo]

Hi, hope some of you gurus can help me please.

I have a large download of data from a Gas meter, which I need to analyse to show a line chart with 3 lines: max, min and avg values for each time slot per weekday (M-F) and per weekend day (S-S). Time slots on X axis (horiz) & value of gas used on Y axis (Vertical).

Data downloads in 2 columns:

14/07/2010 00:00	372.315
14/07/2010 01:00	326.365
14/07/2010 02:00	315.761
14/07/2010 03:00	269.811
14/07/2010 04:00	243.89
14/07/2010 05:00	233.286
14/07/2010 06:00	248.603
14/07/2010 07:00	230.93
14/07/2010 08:00	239.177
14/07/2010 09:00	262.742
14/07/2010 10:00	242.712
14/07/2010 11:00	212.078
14/07/2010 12:00	247.425
14/07/2010 13:00	250.959
14/07/2010 14:00	247.425
14/07/2010 15:00	239.177
14/07/2010 16:00	223.861
14/07/2010 17:00	83.653
14/07/2010 18:00	60.089
14/07/2010 19:00	57.732

...............

16/04/2014 00:00	271.059
16/04/2014 01:00	279.646
16/04/2014 02:00	300.62
16/04/2014 03:00	344.897
16/04/2014 04:00	327.419
16/04/2014 05:00	336.741
16/04/2014 06:00	304.115
16/04/2014 07:00	285.472
16/04/2014 08:00	284.307
16/04/2014 09:00	262.168
16/04/2014 10:00	255.177
16/04/2014 11:00	255.177
16/04/2014 12:00	251.682
16/04/2014 13:00	244.691
16/04/2014 14:00	227.213
16/04/2014 15:00	238.865
16/04/2014 16:00	252.847
16/04/2014 17:00	236.534
16/04/2014 18:00	254.012
16/04/2014 19:00	250.517
16/04/2014 20:00	238.865
16/04/2014 21:00	233.039
16/04/2014 22:00	241.195
16/04/2014 23:00	302.95
17/04/2014 00:00	300.972

Total height of column is nearly 33,000 rows, so very difficult to manually sort this out, even adding prefixes 1-7 etc.

Is there a straightorward way to parse this monster pair of columns into 2 overviews: 1 for workdays (M-F) and 1 for weekends (S-S).

My Excel skills extend to Filter, Sorting, Lookups & various formulas.

Thanks in advance for any help.

JMo

 

[BrianMH]

Add a helper formula to determine if it is a weekend or weekday.

=IF(WEEKDAY(A2,2)<=5,"Weekday","Weekend")

Then just use a pivot table to summarize it based on that column.

 

[BGY23]

Hi,
You can use either of these formuals. Insert a new column and make the formula reference the cell(s) in the date column. I have set mine up in Column a And had the date in Column B.

This one will tell you which day of the week the date is
=TEXT(WEEKDAY(B2),"dddd")

And this one will split it into weekday and SS

=IF(OR(WEEKDAY(B3)=1,WEEKDAY(B3)=7),"SS","Weekday")

 

[DalkeyJMo]

Thanks for that. JMo