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:

[TABLE="width: 269"]
[TR]
[TD="align: right"]14/07/2010 00:00[/TD]
[TD="align: right"]372.315[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 01:00[/TD]
[TD="align: right"]326.365[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 02:00[/TD]
[TD="align: right"]315.761[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 03:00[/TD]
[TD="align: right"]269.811[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 04:00[/TD]
[TD="align: right"]243.89[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 05:00[/TD]
[TD="align: right"]233.286[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 06:00[/TD]
[TD="align: right"]248.603[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 07:00[/TD]
[TD="align: right"]230.93[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 08:00[/TD]
[TD="align: right"]239.177[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 09:00[/TD]
[TD="align: right"]262.742[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 10:00[/TD]
[TD="align: right"]242.712[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 11:00[/TD]
[TD="align: right"]212.078[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 12:00[/TD]
[TD="align: right"]247.425[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 13:00[/TD]
[TD="align: right"]250.959[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 14:00[/TD]
[TD="align: right"]247.425[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 15:00[/TD]
[TD="align: right"]239.177[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 16:00[/TD]
[TD="align: right"]223.861[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 17:00[/TD]
[TD="align: right"]83.653[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 18:00[/TD]
[TD="align: right"]60.089[/TD]
[/TR]
[TR]
[TD="align: right"]14/07/2010 19:00[/TD]
[TD="align: right"]57.732[/TD]
[/TR]
[/TABLE]
...............
[TABLE="width: 269"]
[TR]
[TD="align: right"]16/04/2014 00:00[/TD]
[TD="align: right"]271.059[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 01:00[/TD]
[TD="align: right"]279.646[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 02:00[/TD]
[TD="align: right"]300.62[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 03:00[/TD]
[TD="align: right"]344.897[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 04:00[/TD]
[TD="align: right"]327.419[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 05:00[/TD]
[TD="align: right"]336.741[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 06:00[/TD]
[TD="align: right"]304.115[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 07:00[/TD]
[TD="align: right"]285.472[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 08:00[/TD]
[TD="align: right"]284.307[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 09:00[/TD]
[TD="align: right"]262.168[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 10:00[/TD]
[TD="align: right"]255.177[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 11:00[/TD]
[TD="align: right"]255.177[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 12:00[/TD]
[TD="align: right"]251.682[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 13:00[/TD]
[TD="align: right"]244.691[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 14:00[/TD]
[TD="align: right"]227.213[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 15:00[/TD]
[TD="align: right"]238.865[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 16:00[/TD]
[TD="align: right"]252.847[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 17:00[/TD]
[TD="align: right"]236.534[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 18:00[/TD]
[TD="align: right"]254.012[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 19:00[/TD]
[TD="align: right"]250.517[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 20:00[/TD]
[TD="align: right"]238.865[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 21:00[/TD]
[TD="align: right"]233.039[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 22:00[/TD]
[TD="align: right"]241.195[/TD]
[/TR]
[TR]
[TD="align: right"]16/04/2014 23:00[/TD]
[TD="align: right"]302.95[/TD]
[/TR]
[TR]
[TD="align: right"]17/04/2014 00:00[/TD]
[TD="align: right"]300.972[/TD]
[/TR]
[/TABLE]


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