Converting a CUSTOM M/D/YYYY H:MM Cell Into Time Format

Moderators: Please forgive me if I posted this query in the wrong place. I am unsure if it belongs under the Excel Forum as it deals with formatting cells or the Excel Formulas & Functions as it is needed for a countifs statement. Please forgive me in advance if I have posted this in the wrong location and please relocate it to the correct place. In addition, I apologize if this has been posted before. I have searched the forum and google for many hours before resorting to post on this forum. If it has been posted before, please point me in the right direction.

I need to set up a countifs statement for a column of numbers that fall between a set of dates (i.e. 1/1/2017 – 1/7/2017) and a range of time (i.e. 1600-1659).

I can do this if the cells are inputted as time only. However, the data has been imported from another software package and the cells are formatted in the “custom m/d/yyyy h:mm” format. I copied and pasted the column into another column and formatted it as time (13:30) This did not work as well as I imagine the source formatting did not changed.

Is there a way to convert a cell that is formatted as custom m/d/yyyy h:mm into just time (i.e. 13:30)?

There is a lot of data and I shudder at the thought of having to manually enter in this data.

I know if I do =INT(A2) will convert to date. Is there something similar for time? I am in hopes someone in the forum can assist.

Any and all assistance is greatly appreciated,

Lance

Following formula will extract time only.

=MOD(A2,1)

That worked! Thank you!