Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to fill a cell with the current time, so it changes every time I open the file.

I currently use the now() function for that, but it returns both the date and the time.

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

try this formula

=NOW()-TRUNC(NOW())

=NOW()-TODAY()
share|improve this answer
1  
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
=Now()
  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
1  
@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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.