Extract time and date from 1 cell

**jaywizz** · 09-24-2009, 07:59 AM

/ have a column of cells which has date and time in one cell e.g.

07/11/2009 08:07:22 (I have attached a spreadsheet with some examples)

I can get the information extracted if i use: =TEXT(A10,"HH:mm") for the time and change HH:mm to MMM-DD for the date

However, when I run a Pivot it sorts the date alphabetically rather than by date.

Anyone know how I can get around this?

**billshankley** · 09-24-2009, 08:47 AM

Can you not just copy the cell into two cells then just change the formatting of one of the cells to mm/dd/yyyy and the other to hh:mm or however you want it formatted

**DonkeyOte** · 09-24-2009, 08:48 AM

Time is Decimal, Dates are Integers... eg

=INT(A10) will give you just date

=MOD(A10,1) will give you just time

In Pivots you can Group by various Date Time periods, without knowing your end goal it's hard to know if you need to split the datetime values in the first instance.

For more info. on Grouping Date related fields in Pivots see: http://www.contextures.com/xlPivot07.html

**arthurbr** · 09-24-2009, 08:49 AM

Originally Posted by jaywizz

/ have a column of cells which has date and time in one cell e.g.

07/11/2009 08:07:22 (I have attached a spreadsheet with some examples)

I can get the information extracted if i use: =TEXT(A10,"HH:mm") for the time and change HH:mm to MMM-DD for the date

However, when I run a Pivot it sorts the date alphabetically rather than by date.

Anyone know how I can get around this?

No sheet attached. To extract date use =int(a10) for time =a10-int(a10) - Format as needed