How can I extract an undated time value from a date/time field?

I am working with a long list of readings from a noise meter (1 per sec = 28,800 rows for an 8 hour session)

In column A I have a date/time field = e.g. "12/04/2013 10:37:20". In column B are the readings.
On another sheet, I am doing various calcs on consecutive 5-min (300 row) sets of these readings: and in order to do a chart, I need to put just the TIME section of the date time field (i.e. "10:37:20") into a Time column, to indicate the start time of each dataset.

The only way I have found to do this is to copy the whole date/time column to a text editor, and then copy the truncated column back into Excel. Which is a nuisance, because (a) only I know how to do this, and (b) it has to be done every time new data is introduced.

Question: can anyone tell me a formula or function which will do this automatically within Excel, thus removing the need for manual editing?

This is my first venture into the Forum - so forgive me if this question has already been answered elsewhere...

NPB

To get the TIME from a cell containing Date+Time, use

=MOD(A1,1)

Hope that helps.

Originally Posted by Jonmo1

To get the TIME from a cell containing Date+Time, use

=MOD(A1,1)

Hope that helps.

Thanks for the suggestion. This formula is still giving me a date/time field, but with 01/01/00 substituted as the date. What I need to extract is just the last 8 characters containing the TIME info. Does this work in your more recent version? (I have not updated because I'm worried about VBA compatibilty - and also because I'd rather keep the money!)

Any other ideas?

NPB

Format the cell with the formula as just a time.

Originally Posted by Jonmo1

Format the cell with the formula as just a time.

Of course!

As so often, a simple answer was staring me in the face. Problem solved - many thanks.

NPB

Great, glad to help.

Hahaha,


So glad I trolled through to find this answer as it helps me out 100% on a preadsheet I am working on presently!!

So simple....

Rob