truncate date not time

[buckwheat]

I have a cell that lists the date and time. I need to just pull out the time and discard the date. Any ideas?

 

[Cbrine]

If it's a date time stamp, use this

=TEXT(A1,"MM/DD/YYYY")

HTH
Cal

Whoops, you wanted the time, not the date.
=TEXT(A1,"HH:MM:SS")

 

[barry houdini]

Try

=MOD(a1,1)

where your date/time is in A1

format as time

 

[buckwheat]

Thank you , I am not at work anymore but will try in the morning.

 

[PaddyD]

ADVERTISEMENT

or, being a bit more explicit, and frankly needlessly so:

=time(hour(a1),minute(a1),second(a1))

 

[buckwheat]

Thank you Paddy D, This worked perfectly. The first two suggestions returned 00:00:00 and 0 respectivley.

Thanks again,
Christina
Jax Fl

 

[barry houdini]

ADVERTISEMENT

Thank you Paddy D, This worked perfectly. The first two suggestions returned 00:00:00 and 0 respectivley.

Thanks again,
Christina
Jax Fl

Not quite sure how that could happen, if Paddy's worked then

=MOD(A1,1) must work - or am I missing something?

 

[buckwheat]

Below is a paste of what I had and the result. I put the exact formula in column C that is in Column B. I misstated in my previous email that =Text didn't work when it actually did. I'm not blonde btw, just tired.
SCH_LCL_ACY_TS
A B C = formula in column B
10/27/2005 4:23 4:23 AM =TIME(HOUR(A2),....
10/27/2005 4:23 04:23:00 =TEXT(A3,"HH:MM:SS")
10/27/2005 5:07 1/0/1900 5:07 =MOD(A4,1)

 

[barry houdini]

OK, it's basically a formatting issue.

If you use the formula =MOD(A1,1) it will give you the correct result but you need to format the cell as h:mm (or your preferred time format) to show the time and not the date.

1/0/1900 is what excel shows if you format a blank cell as a date (because excel counts dates from 1st January 1900).

The real question is: what do you want to do with this result?

If you want to use it in a calculation then all 3 formulas would work (although =TEXT(A1,"HH:MM:SS") gives you a text result).

If you simply want to display the time and not the date then you don't need a formula at all, you can just format it to show what you want.

hope this helps

 

[buckwheat]

It does help thank you. Actually it gets more complicated now. This is what I am doing: I have a table that has scheduled arrival in this time format 6.83 which is 6:50 and I have another table with actual arrival in this format 11/03/2005 07:00:00 I need to calculate how late the actually arrival is. Prior to my first post I was not aware that the scheduled arrival was formatted in 100ths