I ran into a painful situation recently where I wanted to graph the number of occurances of a particular event to the hour each day.
I found that providing a list of date time values in one column with it’s number of occurances in the second column was not the answer. Excel automatically prepares the data to be grouped per day.
To get around this, I broke my input date up into columns for the year, month, day and hour.
Then using the following technique, I merged it together into my preferred format.
The syntax is:
=A2&"/"&B2&"/"&C2&" "& D2
Here’s the demonstration of the syntax in the example