How to make Excel count events per day?

Question

My wife is keeping record of the times our baby girl eats and now wants to plot the information in a way that, so far, I was not able to do.

The data consists only of the feeding start times. Don't worry, I truncated the data - she eats more often than this :-)

DESC    TIME
Eat 28/08/2012 20:15
Eat 28/08/2012 23:25
Eat 29/08/2012 04:00
Eat 29/08/2012 07:25
Eat 29/08/2012 10:45
Eat 29/08/2012 20:45
Eat 30/08/2012 03:10
Eat 30/08/2012 06:40
Eat 30/08/2012 10:20
Eat 30/08/2012 13:00
Eat 30/08/2012 15:17

My wife wants to see, per hour, at what the first, second, third (and so on) feeding times are. Using HOUR() I was able to do part of the calculation, but can I automatically create the column FEED OF THE DAY?

DESC    TIME            HOUR    FEED OF THE DAY
Eat 28/08/2012 20:15    20      1
Eat 28/08/2012 23:25    23      2
Eat 29/08/2012 04:00    04      1
Eat 29/08/2012 07:25    07      2
Eat 29/08/2012 10:45    10      3
Eat 29/08/2012 20:45    20      4
Eat 30/08/2012 03:10    03      1
Eat 30/08/2012 06:40    06      2
Eat 30/08/2012 10:20    10      3
Eat 30/08/2012 13:00    13      4
Eat 30/08/2012 15:17    15      5

Once this is done, I can then plot HOUR and FEED OF THE DAY.

Thanks in advance!

Answer 1

Please see the image below.

Cell C1 is hard coded with a 1. This is OK as we know this is the first value.

As of C2, everything is done by Excel.

In C2, I used this formula...

=IF(AND(HOUR(B2) > HOUR(B1), (DAY(B2) = DAY(B1))), C1 + 1, "1")

...and dragged it down to copy the formula to C4.

However, I think you will still have a problem in that this won't recognise when it starts a new month but as I'm not sure if your method of recording data will be month by month or a rolling basis I've only answered your question instead of trying to be clever.