in my cell containing the date i have =NOW(). When the clock goes past midnight, the date changes on the spreadsheet. Is there a way to keep the pre-midnight date for a period of time after (an example to 1 or 2am?)
Hello,
there is no option to to tell NOW()
to stop updating for a certain period of time. The nature of volatile functions is to update on each change event of the document. Thus you need to take care on your own by using a formula what to be shown the 1 or 2 hours after midnight. One(!) solution could look like this:
Assumptions (for the formula in this answer)
- Cell
A1
contains time until freeze occurs (e.g.02:00:00
) - Cell
A2
contains time which should be shown for the freeze period (e.g.23:59:59
)
Formula: =IF(MOD(NOW();1)<A2;INT(NOW()-1)+A1;NOW())
If you want some sort of hard coded values, you can use:
=IF(MOD(NOW();1)<0.083333333333333;INT(NOW()-1)+0.999988425925926;NOW())
where
0.083333333333333
is 02:00:00
--and–
0.999988425925926
is 23:59:59
Reminder A time in LibreOffice Calc is a fraction of a day and thus 02:00:00
is 2/24
~ 0.083
Hope that helps.
If the answer helped to solve your problem, please click the check mark () next to the answer.
I’ missing =INT(NOW()-1/12)
. What’s bad with it?
@Lupp - of course there’s nothing bad with your formula. OP using NOW()
misleadingly made me assume he wants to have the time as well.