Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
can you date time stamp entries in excel | Excel Discussion (Misc queries) | |||
Automatically enter date and time but only update once. | New Users to Excel | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) |