Regarding Formula for Date and time

[Samuel Jacob]

I have a excel column which has date of the year and time. For example in
cell A1 i have 03/05/2004 12:42. Like this i have 152 cells and every cell
has got date and time in it. Now i need to seperate all the 152 cells based
on the weekday and the hour in a table. The table format looks like this:

Mon Tue Wed Thr Fri Sat Sun
0000-0059
0100-0159

 

[Bob Phillips]

Put this formula in the Mon, 0000-0059 intersect cell, and copy down and
across

=SUMPRODUCT(--($A$2:$A$200<>""),--(WEEKDAY($A$2:$A$200,2)=COLUMN(A1)),--(HOUR($A$2:$A$200)=ROW($A1)-1))