Basic excel date and time problem

Question

Hi everyone,

Have a list of dates in excel in the format (this comes originally from csv):

23/11/09 07:27:02
23/11/09 08:01:50
23/11/09 08:38:58
23/11/09 09:40:01

What I want to do is count the number of these falling between hour blocks, like 7-8, 8-9, 9-10 etc Not sure how to get started, but one idea was just to put logic statements comparing the dates between these blocks, then adding the total "trues"

I can't get it to compare properly. When I type it the hour block marks,

    e.g. 23/11/09 08:00 
excel actually shows that as 
23/11/2009 8:00:00 AM

and the compare doesn't work. Well actually it does the opposite of what it should.

that is:

=IF(C5>L1,IF(C5<M1,TRUE,FALSE),FALSE)

C5 being date in top codeblock, L1 and M1 being the hour blocks I manually entered in the second code block.

Has anyone got any ideas?

Answer 1

=hour(a1)=7

will return true if the time of the date/time value in cell A1 is between 7 and 8 (AM) and will otherwise return false.

Answer 2

Excel stores dates as number of days since 1900 or 1904 depending on your setting and the time as a fraction of the days. So 11:59 am 4th of July 1960 is held internally as '22101.4993055556'.

As such you cannot do plain charactrer string comparisons on dates. However ther ar lots of nifty time/date functions available to you.

You probably want :

=IF(HOUR(B1) > 8,IF(HOUR(B1)<12,"YES","NO"),"NO")

Answer 3

You should use Excel functions, like HOUR(), to extract the parts of the times, and apply the logic tests to those extracted values.