Copy portion of cell involving date and time

[lilmish]

Cell A1 contains the following: 6/6/2012 4:00:00 PM

The formatting of Cell A1 is custom m/d/yy h:mm

I would like to paste only the 4:00:00 PM portion of cell A1 into a new cell

I understand generally the concept of string functions, but have not had much experience using them and can't seem to figure this out.

Any help would be much appreciated!

 

[Tetra201]

=TEXT(A1,"h:mm:ss AM/PM")

 

[Joe4]

I understand generally the concept of string functions, but have not had much experience using them and can't seem to figure this out.

The reason why you cannot use String functions on it is because it is not a string entry, but rather a numeric entry with a date format.
In Excel, all dates and times are stored as numbers, specifically, the number of days since 1/1/1900 (time is just a fractional portion of one day).
If you take any valid date/time entry, and change the format to "General", you will see it as Excel sees it. And this is the value that any String functions would run against.

The TEXT function converts the entry from a valid date/time entry to a text entry, in the specified format. Note that the result of using the function is a TEXT value. So any date/time functions or math would not work against it.

Note that you could simply copy the value to another cell and just apply a Custom Format of h:mm:ss AM/PM on it to just show the time. It would still retain the whole date/time value, but just show the time piece. The advantage to this method is that it is still a valid date/time function, so you can still apply date/time/math functions to it, if needed.

 

[JoeMo]

If you only want the time portion, irrespective of the date try:

=MOD(A1,1)

and format the formula cell as Time.