Prevent Now() in Formula from Updating

[dkinley]

Since I know hardly anything about Excel, I tried to apply VBA logic and I am getting mixed results. I have two cells, one takes in a time offset, the other adds that to the current time.

Cell1 takes in a time offset in the form HH:MM. Cell2 uses the formula: Now() + Cell1. This initially works.

Where it doesn't work is when the system time increments one minute, Cell2 also increments - so it doesn't work as a one-off like I thought it would. It also updates all the rows when the spreadsheet is closed and reopened.

How can I make this a one-off computation without it updating?

-dK

 

[namliam]

You cannot

 

[dkinley]

lol .. Yes I can!!!

Crud.

Guess I am going to pound my head in the wall for a bit trying to create something will let me - was hoping there was an easy way.

-dK

 

[namliam]

Well not with the functions in excel like you are asking.

Perhaps it is an option to write the Now() value from VBA into the cell as an actual value, however having it as a formula will recalculate it every time

 

[dkinley]

That's the path I am going down right now. I don't use spreadsheets that much and didn't realize the formula recalculating aspect of it.

-dk

 

[namliam]

Well that is the power (and offcourse the weakness) of excel It helps you by doing some work for you, however if you dont want that work to be done.... there is no way of actually stopping it....

Good luck

 

[dkinley]

...there is no way of actually stopping it....

hehehe ... once in motion ...

Thanks for the help, I will post back if i finger it out.

-dK

 

[namliam]

No not even a matter of "once in motion", you cannot even set it to start it is just always running period....
Offcourse you can turn off the auto calcs but then all formula's are stopped, not just the one. (Tools => Options => Calculation tab) and once off you can only recalculate ALL not just the one...

Guess the "once in motion" applies though as it starts moving the moment you start it up. Excel really bites sometimes.... in particular when interchanging/interacting with access..... "normal" users simply have no clue sometimes

 

[dkinley]

Oh yeah, welcome back by the way

Think I have the problem resolved with the following code ...

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Columns("C:D"), Target) Is Nothing Then
        If Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
        Application.EnableEvents = False
        Cells(Target.Row, Target.Column + 2) = Target.Value + Now()
        Application.EnableEvents = True
    End If
 
End Sub

Going to write a narrative for others who aren't as familiar (like me) ...

- The sub checks to see if anything has been changed on the worksheet.
- The first IF limits this check to 2 columns (C & D).
- The second IF prevents the sub from getting stuck in a loop.
- The Cells(...) = Target... line is what makes it work. It takes the cell that was changed, adds the current time to it, and places the value (not a formula) into a cell two columns over on the same row.

The placing of the value prevents the update from occurring as if you typed it in manually.

Note, the (Target) cell that is changed is a time formatted cell. So if I was to add an hour and half to the current system time, I put in 1:30. The destination cell is formatted as time so it simply adds the appropriate hour and minute without any housekeeping on my part.

-dK

 

[namliam]

Which does what I suggested earlier.

Perhaps it is an option to write the Now() value from VBA into the cell as an actual value

Good job on the code though, I probably would not have found anything like that.

 

[DanG]

I don't know how many times I've needed to do that and just gave up!

But taking a moment to think about it, couldn't you also (through VBA) enter the date in the cell using Now() and then Copy/Paste as values (using VBA)?

Thanks for the post!

 

[dkinley]

Yes I could have, but that wasn't my requirement.

The contstruct of my spreadsheet was that columns A and B were manually entered. Example is 1:30 and 1:45 which indicates there is a 15-minute window that starts an hour and half from now and ends an hour and forty five minutes from now.

The time computation was performed by the code I posted and the result placed in columns C and D respectively. Since there are circa 200 distinct projections, I wanted to automate it somewhat and have the fields behave statically to preserve the information for later analysis. So I could develop the algorithm on the time when the window was forcasted, the length of the forecast and the actual event occurring.

Awesome that you might get some use out of it. I hate spending time doing something and it only gets used once.

-dK

 

[DanG]

Thanks again!

 

[namliam]

Awesome that you might get some use out of it. I hate spending time doing something and it only gets used once.

LOL, you may be surprised at what posts get re-used...

I recently got a "thank you" post on some thread that was 4 years old... This person had read the problem, followed the solution I provided and solved his problem. It is rare that you actually get a post like this.... but I get a feeling that 'old help' is more usefull than just sitting there in the archives.

 

[Brianwarnock]

LOL, you may be surprised at what posts get re-used...

I recently got a "thank you" post on some thread that was 4 years old... This person had read the problem, followed the solution I provided and solved his problem. It is rare that you actually get a post like this.... but I get a feeling that 'old help' is more usefull than just sitting there in the archives.

That's why it p&**es me off when after a number of suggestions, maybe by different people, the thread just stops, no info as to whether any solved the problem.

Brian

 

[namliam]

That's why it p&**es me off when after a number of suggestions, maybe by different people, the thread just stops, no info as to whether any solved the problem.

Yes a small, thank you with our without "I fixed it this way" or "This is my final working code" or stuff like that would be nice...