Take the 2-minute tour ×



I want to see only the time. Is there a way to do that?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

try this formula


share|improve this answer
Note that TRUNC(NOW()) = TODAY() –  barry houdini Mar 25 '12 at 20:21
This gives me some kind of digital number, unless I format the cell as date... But I don't want to have to format the cell explicitly. With the now() function, the cell formats without any external intervention... –  Lea Cohen Mar 25 '12 at 20:27
In both cases the underlying data is a Date Serial Number. In the first case Excel is just applying the format for you. You have no option but to apply a format. –  chris neilsen Mar 25 '12 at 20:30
@Lea_Cohen you could use TEXT function to get the time, e.g. use =TEXT(NOW(),"hh:mm") - change "hh:mm" part as required - no formatting required (although the cell now contains text rather than a time - not crucial if this is for display) –  barry houdini Mar 25 '12 at 20:38
@Barry that will do it. (My guess - next question: how do I use that time in a formula) –  chris neilsen Mar 25 '12 at 20:42
show 3 more comments
  1. Right Click on Cell
  2. Click Format Cells
  3. Click on Custom on left side
  4. Select the time format as h:mm:ss AM/PM
  5. Click OK

One more Solution:-

=TEXT(NOW(), "hh:mm:ss AM/PM")
share|improve this answer
Thanks, but this is what I do now. What I'm looking for is a way that will save me these 5 steps... –  Lea Cohen Mar 25 '12 at 20:28
Why not write a macro that does these 5 steps then? –  dsolimano Mar 25 '12 at 20:31
@LeaCohen: I have updated my answer with one more solution. –  Siva Charan Mar 25 '12 at 20:40
@Siva_Charan Snap! see my comment........... –  barry houdini Mar 25 '12 at 20:42
@barryhoudini: Sorry I didn't noticed your comment. Since you have posted 1 min before me, should I remove my answer. –  Siva Charan Mar 25 '12 at 20:43
show 2 more comments

Your Answer


