Counting States basied on the time and date stamp
 

Afternoon All

I am attempting to workout a quick and easy way of counting some results
that are displayed in an excel spreedsheet. All of the results are date and
time stamped, and also have a column that showes the state of the outcome
[WA, NSW, QLD and so on]. What i am wanting to do is get a count basised on
the state column for every hour?

Can anyone assist? Cheers TJ

Hi, TJ-
I started a reply earlier this morning, and abandoned it hoping someone
else would have a better idea. Since no one else has replied, let me
take a crack at it.

It can be done, altho it may seem a bit clumsy because of the way Excel
treats dates and times. When you enter a date into Excel, the software
converts it to a serial number. A date is an integer number; a time is
a fraction of a day. In my time zone (East coast, USA) the time right
now is 7/7/2005 10:44 am, and its serial equivalent is 38540.45. You
can try this yourself by entering a timestamp into a cell, then
reformatting that cell from a date to a number. 38540 refers to the
date; the decimal number refers to the time. Convert the time to this
decimal representation like this (10/24) + (44/60/24).

So if your table of timestamped data is in Sheet1, you would need a
summary schedule, perhaps on a different tab, with a date header and
then a series of time reference points. You'd need to search the
timestamps for a date match and simultaneously search for matches
within a hour range: greater than or equal to 0/24 and less than 1/24
(for the hour between midnight and 1 am), greater than or equal to 1/24
and less than 2/24 (for the hour between 1 am and 2am), greater than or
equal to 2/24 and less than 3/24 (for the hour between 2 am and 3 am),
etc.

You can make this job somewhat easier if you have the latitude within
your spreadsheet to split the date from the time- can you do that?
That is to say, are you able to and do you have the authority to do so?

If you'd like, send me some sample data from an actual sprdsht- no
macros- and I'll show you what I mean. This will likely require a
combination of different functions that are easier to show than
explain.

Dave O
cyclezen at yahoo dot com