I have a cell with a basic formula, "=now()", which displays the time and date in a specified format. How can I keep that cell from updating automatically every time I open the file? I have a macro set to calculate the value on command, but it also updates on open, which I don't want.

Thanks in advance,

-Joe

Hi, NOW() is one of Excel's volatile functions. It updates on Open and Close and pretty much on all changes in the workbook that affect calculation.

You can't change that functionality.

You can use a macro to enter a date/time in a cell, as a value (not as a formula) and it will never change.

Please post the code in your macro, for further advice.

Regards

Very simple, one line. Range("C4").Calculate

It causes it to recalculate the "=now()" function.

How do I make a macro write the value into a cell that would come up using "=now()" ?

Thanks,

-Joe

Hi, In VBA these two lines will enter a time and set the format:

Range("C4").Value = Time Range("C4").NumberFormat = "hh:mm"

The time in cell C4 will not change unless this bit of code is run.

It will not change when the workbook is opened or closed etc.Regards

Is there a command I can substitute for "Time" that will display both the date and the time?

Never mind, I just tried using "now" and it worked. Thanks for your help.

You're welcome Regards

Humar

Now how do I get the macro to do several different things? Like all 3 of these? It doesn't work to just have those one after the other. Range("C4").Value = Now

Range("C4").NumberFormat = "yymmddhhmm"Range("C1").Value = Today

Range("C1").NumberFormat = "mm/dd/yyyy"Range("A51").Value = Today

Range("A51").NumberFormat = "mm/dd/yyyy"Thanks again.

Hi, If you had used Option Explicit like this:

Option Explicit Sub Times() Range("C4").Value = Now Range("C4").NumberFormat = "yymmddhhmm" Range("C1").Value = Today Range("C1").NumberFormat = "mm/dd/yyyy" Range("A51").Value = Today Range("A51").NumberFormat = "mm/dd/yyyy" End SubYou would have received a 'Variable not defined' error for 'Today'

That's because Today is not a VBA command and VBA has treated it as a variable with no value.

So the macro ran all the lines - C1 and A51 have been formatted, but no value was entered in those cells.

As Date information is in the Integer part of the value returned by Now, try this:

Option Explicit Sub Times() Range("C4").Value = Now Range("C4").NumberFormat = "yymmddhhmm" Range("C1").Value = Int(Now) Range("C1").NumberFormat = "mm/dd/yyyy" Range("A51").Value = Int(Now) Range("A51").NumberFormat = "mm/dd/yyyy" End Subor use the VBA Date function:

Option Explicit Sub Times() Range("C4").Value = Now Range("C4").NumberFormat = "yymmddhhmm" Range("C1").Value = Date Range("C1").NumberFormat = "mm/dd/yyyy" Range("A51").Value = Date Range("A51").NumberFormat = "mm/dd/yyyy" End SubRegards

Didn't know you couldn't use "Today" in VB (I'm new to this, as you can tell). Here's the script that worked for what I needed to do:

Sub GeneratePONumber()

Range("D4").Value = Now

Range("D4").NumberFormat = "yymmddhhmm"Range("C1").Value = Now

Range("C1").NumberFormat = "mm/dd/yyyy"Range("A51").Value = Now

Range("A51").NumberFormat = "mm/dd/yyyy"

End SubThanks,

-Joe

Hi, You realize that when you use Now you are putting both a date and a time value in each cell.

It looks from your formatting that you only want the date information in cells C1 and A51.

In which case use Date

notNowNow will work, but you may get unexpected results later

If you run this:

Range("A1").Value = Now Range("A1").NumberFormat = "mm/dd/yyyy"Then edit the same code to this:

Range("B1").Value = Now Range("B1").NumberFormat = "mm/dd/yyyy"and run it.

(Don't try this with all 4 lines in one subroutine - you need to run it twice - once for col. A and once for col. B)In the worksheet, cells A1 and B1 will both show today's date - they will appear identical.

In another cell enter this formula:

=IF(A1=B1,"Equal","Not equal")

The result is 'Not equal'If you do the same with Date instead of Now, the result will be 'Equal'

Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History