Current Time

  • Thread starter Thread starter Steevo
  • Start date Start date
S

Steevo

Does anyone know if there is a formula for displaying the current time? I
know =TODAY() exists for displaying the date and also =NOW() for displaying
both the date and time although I cannot find any way of displaying just the
time on its own.


Many thanks in advance
 
Use =NOW() and format the cell as hh:mm

If you don't want the date in the underlying data, then you could use =NOW()-TODAY(), but not sure
what it buys you that the formatting won't.
 
Ken Wright said:
Use =NOW() and format the cell as hh:mm

If you don't want the date in the underlying data, then you could use =NOW()-TODAY(), but not sure
what it buys you that the formatting won't.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------------------------- --
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------------------------- --
Click to expand...

Thanks, unfortunatly that doesn't help me out very much (or at least i can't
see if it does). You see, what I am actually trying to do is conditional
formatting on a cell, marking it a certain colour if todays date is after
the date in A1 and the time is also after the time in F1. The current
formula I have tried is:

=AND($A1<=TODAY(),$F1<=NOW())

Unfortunatly this will not work because the now() value includes the date
and the number representing it therefore is a number about 38000 whereas the
value in F1 does not include a date and is therefore a very small decimal of
0.833333...


Is there any way that I can represent the current time in a conditional
formatting formula in order to achieve this?

Many Thanks
 
Peo Sjoblom said:
Did you try Ken's suggestion?

NOW()-TODAY()

you can also use

=MOD(NOW(),1)

--

Regards,

Peo Sjoblom
Click to expand...

Yes, I did try Ken's suggestion although, like i said the now()-today()
gives you a much larger number than a time figure of 20:00 which has a value
of 0.83333....


What does the MOD function do?
 
No it does not give you a much larger number..
One day equals 1 in Excel, thus one hour equals 1/24 = 0.0416666666666667
20:00 is 20 x 0.0416666666666667 = 0.833333333333333

TODAY() = 37905

NOW() = 37905.2848997685

NOW() - TODAY() = 0.2848997685

0.2848997685 formatted as time h:mm = 06:50 (US Easter time)

So either you don't have real date/time or must have applied it incorrectly

TODAY() used the internal computer clock and returns todays date at 00:00:00
while NOW() returns todays date plus the time when the formula was
calculated..

MOD will return the same result
 
Steevo,

Be aware that this time gets stale if a calculation isn't performed for a
while. Time stops.
 
Earl Kiosterud said:
Steevo,

Be aware that this time gets stale if a calculation isn't performed for a
while. Time stops.
Click to expand...

What sort of timescale are we talking here, I mean would it be ok if left
for a week or so?

Many Thanks
 
Peo Sjoblom said:
No it does not give you a much larger number..
One day equals 1 in Excel, thus one hour equals 1/24 = 0.0416666666666667
20:00 is 20 x 0.0416666666666667 = 0.833333333333333

TODAY() = 37905

NOW() = 37905.2848997685

NOW() - TODAY() = 0.2848997685

0.2848997685 formatted as time h:mm = 06:50 (US Easter time)

So either you don't have real date/time or must have applied it incorrectly

TODAY() used the internal computer clock and returns todays date at 00:00:00
while NOW() returns todays date plus the time when the formula was
calculated..

MOD will return the same result

--

Regards,

Peo Sjoblom
Click to expand...

Thanks for the help gyus. In the end I actually came up with a nice and
simple solution based on the facts that Peo (thanks!) had told me about the
way dates are formatted. I decided to just use the formula:

=TODAY>=A1+F1

As far as I can see it works no trouble. Thanks again for telling me about
the date formatting Peo!!


Many Thanks
 
Steevo,

If a calculation is done, the time will update. But an hour later, if
nothing has caused a calculation (like when you wake up from your nap, or
get back from lunch), the time will still be what it was at the last
calculation.
 
Back
Top