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
A1contains time until freeze occurs (e.g.02:00:00) - Cell
A2contains 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.