Current Time

[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

 

[Ken Wright]

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.

 

[Steevo]

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
-------------------------------------------------------------------------- --

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]

Did you try Ken's suggestion?

NOW()-TODAY()

you can also use

=MOD(NOW(),1)

 

[Steevo]

Did you try Ken's suggestion?

NOW()-TODAY()

you can also use

=MOD(NOW(),1)

--

Regards,

Peo Sjoblom

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?

 

[Peo Sjoblom]

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

 

[Earl Kiosterud]

Steevo,

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

 

[Steevo]

Steevo,

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

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

Many Thanks

 

[Steevo]

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

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

 

[Earl Kiosterud]

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.