15

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?

5

After you entered the formual =now() to the cell, select the cell and press Ctrl + Shift + 2 You're welcome!

  • 2
    Note that this only formats the cell as time. The underlying value is still Date and Time – chris neilsen Dec 13 '17 at 22:20
  • Just adding on, Just set the type of cell to date or long date or time as needed to play around with different type of values. – Alok Rajasukumaran Jan 15 at 10:03
13

try this formula

=NOW()-TRUNC(NOW())

=NOW()-TODAY()
  • 2
    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
  • 1
    @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
2
=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")
  • 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
1

You could try the following:

TIME(VALUE(LEFT(TEXT(NOW(), "HH:MM:SS"), 2)), VALUE(LEFT(RIGHT(TEXT(NOW(), "HH:MM:SS"), 5), 2)), VALUE(RIGHT(TEXT(NOW(), "SS:MM:SS"), 2)))

This will effectively break up the hours, minutes and seconds returned from the format function and format them as a time. I haven't tried it in the afternoon, so I'm not sure if you get problems with am/pm.

  • Please edit your answer to included the corrected formula – DavidPostill Sep 9 '16 at 8:30
1

Try this code:

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Thanks.

0

To just show the time you would hold --> CTRL + : <-- this will display the time only.

  • Hi Admingrl, and welcome to SuperUser. Your answer is great if I want a shortcut for displaying the time one-time-only. But I was looking for a way to display the current time dynamically - that each time I open the spreadsheet, the cell will show the current time. – Lea Cohen Feb 1 '15 at 7:12
0

Use

=NOW()-TODAY(),

If you want to use it in a formula like "end time" - "current time" to find "total hours" then just format the "total hours" cell to number and multiply the whole formula by 24 like this

Current Time   Finish Time     Total Hours

=NOW()-TODAY()  8:30 PM         =(B2-B1)*24
-1

here is a link showing how to do this CLOCK WITH AUTOMATIC REFRESH

in line With Sheet1.Range("A1") replace Sheet1 with actual sheet name where you want this clock and A1 with cell where you want your clock

  • 1
    Welcome to SuperUser. If you post an answer that's just a link, it's harder for people to see whether it will be helpful for them, and it can become meaningless if the target website goes offline. It's better to quote the important information directly, and just give the link for further reading. – JRI Mar 9 at 17:56
-3

There's a built in function for this, to only provide the time. The function is =Time(). This keeps it from displaying the date, and will only display the current time of the OS upon the creation of a new entry.

  • 1
    Have you tried this? In what version of Excel does it work like that? – G-Man Dec 5 '17 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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