date and time help needed

[Dennisr53]

Hi everyone,
First: I am trying to separate 07/02/2016 02:10:28 into a date and time column any suggestions?

next: I want to count how many are between 2am and 3am I have seen this somewhere but, do not remember where I saw it. Thanks, Dennis

<tbody>
</tbody><colgroup><col></colgroup>

 

[Jonmo1]

First step, to split it up..

A1 contains your date/time value
B1: =INT(A1)
C1: =A1-B1

Format B1 as Date, C1 as Time.

 

[Jonmo1]

Then to count between 2am and 3am

Assuming XL2007+
=COUNTIFS(C1:C100,">=02:00:00",C1:C100,"<=03:00:00")

 

[Dennisr53]

Thanks for the assist Jonmo1 this is going to make my life a lot easier. Dennis

Then to count between 2am and 3am

Assuming XL2007+
=COUNTIFS(C1:C100,">=02:00:00",C1:C100,"<=03:00:00")

 

[Jonmo1]

You're welcome.