Mixed messages

[mikecox39]

I know that [=NOW ()] delivers the current date and time; depending on how the cell is formatted, but that function is dynamic and, if left in the cell, changes over time.


I read somewhere that you can copy the result of that formula the paste it back, as data, to convert it to as static date.


So I tried to create a macro that does that and was successful. However I got a lot of mixed messages in the process.

Is this the easiest way to create a current date and time? I was thinking of using this macro as a kind of Function, that I could "call" from other macros to enter my dates.


https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

 

[alansidman]

A quick alternative if you are looking to hard code date is to Ctl + semi-colon. If you want to hard code the Time then Ctl + Shift + colon

 

[eduzs]

To write current date and time the VBA will be like that:

Sub Test
activesheet.range("A1")=now()
End Sub

 

[mikecox39]

To write current date and time the VBA will be like that:

Sub Test
activesheet.range("A1")=now()
End Sub

Yes, but won't that change as the current time changes?

 

[alansidman]

Confused now. In your first post you discuss a constant Date and Time -- Is that what you want or do you want one that changes? If constant then look at my post #2 .

=Now() formatted as time will give the current time every time the sheet is recalculated.

 

[gravanoc]

I think what you are looking for is something like this:

Public Function DateNow() As Date

    ActiveCell.NumberFormat = "[$-en-US]m/d/yy h:mm AM/PM;@"
    DateNow = Now


End Function

You would place that in a regular Module, not on a sheet. It will give you a static date & time. Then, type =DateNow() on your worksheet instead of =NOW().
The only problem is that the formatting doesn't seem to be taking effect. Anyone know why?

 

[Michael M]

Mike you could also use

Sub Test()
    With Range("A1")
        .Value = Now()
        .Value = .Value
    End With
End Sub

 

[mikecox39]

A quick alternative if you are looking to hard code date is to Ctl + semi-colon. If you want to hard code the Time then Ctl + Shift + colon

Yes, I am familiar with these shortcuts but they don't work in a procedure. Here is what you get when a macro is recorded using the time shortcut:

ActiveCell.FormulaR1C1 = "9:01:00 PM"

I want the macro I create to enter the current time in the activecell, not the one I recorded when I recorded it. I need to change that line to something that results in a current date as data.

I can't use the =now() function because it changes over time, which is why I was using the cut and past/as data option. I just wondered if there was a more efficient method.

fyi Since posting this I removed all Modules and started over. I'm not getting error messages now.

 

[mikecox39]

=Now() formatted as time will give the current time every time the sheet is recalculated.

I want current time that is static, saved a data, that doesn't change when the sheet is recalculated.

 

[mikecox39]

. Then, type =DateNow() on your worksheet instead of =NOW().

Why would I want to create a macro where I would have to enter code on my worksheet? The point of a macro is for it to do all that without my input.