Pl help to fix this problem - Data validation

T

tamilan

I have used data validation list to enter time in cells with reference
to A1 current time =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

When the excel sheet is minimised the work book is inactive and the
time is not updated, it shows the previous time in which its minimised

for eg if i minimise at 11:36:32 AM and open sheet at 11:39:32 AM

the start time should be 11:39:32 AM and end time should be the time at
which the task ends, but excel takes the start time as 11:36:32 AM and
end time as 11:39:32 AM

please help to solve the problem
 
D

Dave Peterson

I'm not sure what you're doing, but
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
is the same as:
=now()

And this is a function that only reevaluates when the worksheet
recalculates--minimizing and maximizing aren't enough to force a recalculation.

F9 will force a recalc, though.
 
J

JE McGimpsey

Dave -

I'm not sure what the OP is doing, either, but

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

is clearly FALSE.

NOW() includes the date.

However,

= TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) =
(ROUND(MOD(NOW(),1)*86400,0)/86400)

*is* TRUE.
 
D

Dave Peterson

You're absolutely correct.

=mod(now(),1)

Would be another way to get the time (w/o the date).
 
Top