Format time column to remove dates

[rory_r]

Firstly, Thanks for any advice given this forum is covered by gods who
watch over the rest of us and have assisted me many times.

I'd like to remove all but the time from a column of dates and times and
have looked into the format function to no avail.

This is the current format for the column copy pasted into Excel.
Wed Dec 31 00:56:28 EST 1969

I can use a 3rd party macro program to perform the following commands
F2 -to enter cell,
CNtrl/Left/left, left - to got to end of required time.
Shift/End -Highlights 'space EST 1969'
Backspace, - Deletes
Cntrl/left - Jumps over time
Shift/home - Selects 'Wed Dec 31 '
Backspace - Deletes
enter---to go to next cell down.

I'd love to perform this in Excel as it would be faster and safer and I
could incorporate it into other Macros I'm using.

I'd also like to perform the opposite on the date column to remove the
bogus times.
Sat Jan 17 00:00:00 EST 2009

Any ideas would be greatly appreciated.
Thansk
Rory

 

[Bernard Liengme]

Remember that Excel stores dates as serial numbers.
If A1 is displaying 01/Feb/2009 14:24, the cell's stored value is 39845.60
So =INT(A1) returns just the date while =MOD(A1,1) returns just the time
Best wishes

 

[Dave Curtis]

Hi,

Presumably Excel is not recognising the enrty as a date/time because of the
EST string? Or is that part of a custom format?

Dave

 

[Dave Curtis]

If the data is text, and is always the same length, you could try

=TIMEVALUE(MID(A6,12,8))

and apply the required time format which would give you 00:56:28

 

[Dave Curtis]

Hi,

And if you want to remove the time and leave the date, try

=DATEVALUE(MID(A6,9,2)&"/"&MONTH(1&MID(A6,5,3))&"/"&RIGHT(A6,4))

and apply a custom format of ddd dd mmm yyyy which will give you

Wed 31 Dec 1969 as a date

Dave