truncate date not time

buckwheat

New Member
Joined
Feb 20, 2003
Messages
21
I have a cell that lists the date and time. I need to just pull out the time and discard the date. Any ideas?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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")
 
Upvote 0
or, being a bit more explicit, and frankly needlessly so:

=time(hour(a1),minute(a1),second(a1))
 
Upvote 0
Thank you Paddy D, This worked perfectly. The first two suggestions returned 00:00:00 and 0 respectivley.

Thanks again,
Christina
Jax Fl
 
Upvote 0
buckwheat said:
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?
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,480
Messages
6,172,505
Members
452,463
Latest member
Debz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top