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?

 

[barry houdini]

You can convert a time in decimals to a true time in excel by dividing by 24 so you could do something like this

Book1
	A	B	C	D
1	scheduled	actual	late
2	6.83	11/03/2005 07:00	00:10:00
3
4
Sheet2

Formula in C2

=MOD(B2,1)-A2/24


There are a few things you may want to bear in mind

If you want to show the lateness in minutes not time format then use

=MOD(B2,1)*1440-A2*60 instead

E.g. given your example this would just give you 10

This has the advantage of allowing you to display a negative figure (if arrival is early) which the first formula won't

...also note that 6.83 isn't exactly 06:50 so you won't get exactly 10 minutes as a result unless the underlying value is actually 6.833333 etc.

 

[buckwheat]

Thanks Barry, I just tried this and it worked perfectly. I should have figured this one out myself. The Excel help information is not very good btw.
Christina