Counting Date AND Time instances

[MostlyClueless]

Hi guys,

I need some gentle guidance please on a formula problem I'm suffering from.

At the moment I have a huge single column of data which is imported which gives me a date and time in the following format:

dd/mm/yyyy hh:mm

My data ranges from about 2010 to the present day, what I want to be able to do is find out how many instances fall within certain ranges.

IE I want to know how many instances of date/times fall within:
Jan 2010 - between 09:00-17:29
Jan 2010 - between 17:30-08:59

So, not day specific, but month specific for each year.
Any idea's you bright bunch? xx

[dilipandey]

Hi MostlyClueless,

IE I want to know how many instances of date/times fall within:
Jan 2010 - between 09:00-17:29
Jan 2010 - between 17:30-08:59

Do you want to know the number of hours or minutes in between these days ? Can you share an example? Thanks.

Regards,
DILIPandey
<click on below * if this helps>

[Norie]

You could add an extra column with a formula that returns something like AM/PM.

Please Login or Register  to view this content.

Then you could create a pivot table with the date/time column as a row label, the new column as a column label and a a count of the new column as a value field.

To get the monthly results you can group the date/time by month.

[MostlyClueless]

Thanks for the replies, to clarify what I'm doing.

I have a list of jobs that have been completed by my engineers. One column has the date AND time of the completion. i.e.

Column A (dd/mm/yyyy hh:mm)
25/01/2013 21:25
25/01/2013 19:38
25/01/2013 18:33
25/01/2013 16:17
25/01/2013 15:09

In one cell I want to know how many jobs were completed in January 2013 during core hours (i.e between 09:00 and 17:29) in another column I want to see how many were done in January 2013 out of hours (i.e between 17:30 and 08:59). The reason for this is that I want to be able to prove a trend that we are doing more and more work 'Out of hours' and have the figures to back it up. Month on month, year on year.

Does that make it clearer?

[Norie]

A pivot table could summarize all the data.

See the attached.

[MostlyClueless]

Norie despite being a novice that definitely looks like something I can work with,

Thank you very much for your help (and thanks to dilipandey for responding also).

[MostlyClueless]

Norie despite being a novice that definitely looks like something I can work with,

Thank you very much for your help (and thanks to dilipandey for responding also).

[dilipandey]

Thanks to Norie...

MostlyCluless, please mark this thread as [SOLVED], if your query has been resolved.

Regards,
DILIPandey
<click on below * if this helps>