19

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?

Improve this question

10 Answers 10

Reset to default
8

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

Improve this answer
2
  • 7
    Note that this only formats the cell as time. The underlying value is still Date and Time
    – chris neilsen
    Dec 13, 2017 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, 2019 at 10:03
20

try this formula

=NOW()-TRUNC(NOW())

=NOW()-TODAY()
Improve this answer
9
  • 2
    Note that TRUNC(NOW()) = TODAY()
    – barry houdini
    Mar 25, 2012 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, 2012 at 20:27
  • 1
    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, 2012 at 20:30
  • 2
    @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, 2012 at 20:38
  • 1
    Yes, as soon as you do a mathematical operation (like subtraction) that co-erces the value.....but it wouldn't work in a comparison with another time without using +0, e.g. =A1>TIME(23,59,59) - where TEXT function is in A1 - that's always TRUE, need to use =A1+0>TIME(23,59,59) to get correct results
    – barry houdini
    Mar 25, 2012 at 20:55
3 
=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")
Improve this answer
6
  • 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, 2012 at 20:28
  • Why not write a macro that does these 5 steps then?
    – dsolimano
    Mar 25, 2012 at 20:31
  • 1
    @LeaCohen: I have updated my answer with one more solution.
    – Siva Charan
    Mar 25, 2012 at 20:40
  • @Siva_Charan Snap! see my comment...........
    – barry houdini
    Mar 25, 2012 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, 2012 at 20:43
2

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.

Improve this answer
1
  • Please edit your answer to included the corrected formula
    – DavidPostill ♦
    Sep 9, 2016 at 8:30
2

Try this code:

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

Thanks.

Improve this answer
0

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

Improve this answer
1
  • 1
    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, 2015 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
Improve this answer
0

In time-related calculations you could use the following formula:

=MOD(NOW(); 1)

It returns the same value as @ChrisNeilsen's formula, but using less steps and getting the current date only once. The downside of this formula is the use of division.

  • To display this value as time, format a cell as Time or Duration.
  • To get the time as a fractional number of hours, multiply this formula by 24.

In Google Sheets you could use the following hack instead:
(the TIMEVALUE() function expects a string, but it works anyway)

=TIMEVALUE(NOW())
Improve this answer
-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

Improve this answer
1
  • 2
    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, 2019 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.

Improve this answer
1

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.