Now Function vs. Today Function

[reberryjr]

I'd like to track the date that an action was taken in a workbook in cell B2, but I don't want the date to update after the initial entry. Will I need to add code that copies B2 and pastes special values, or is there another alternative? I'm not seeing anything on the web that offers guidance on this.

 

[Joe4]

It will require VBA, as an Excel formula will return the current day/time, so it is constantly moving.

I'm not seeing anything on the web that offers guidance on this.

Search on "Excel VBA Date Stamp".
They are literally hundreds of threads on the topic on this forum (and the internet).

 

[reberryjr]

Thanks for the feedback! I looked at Date Stamp prior to posting, and again after your post, but I'm not seeing anything that indicates the date won't change.

 

[Joe4]

Just a few:
https://www.mrexcel.com/forum/excel-questions/614541-excel-time-date-stamp-cell-change.html
https://www.mrexcel.com/forum/excel...-column-upon-data-change-first-time-only.html
https://www.mrexcel.com/forum/excel-questions/1028256-date-author-stamp-several-cells.html
https://www.mrexcel.com/forum/excel...g-date-time-stamp-when-data-entered-cell.html

If you would like help more specific to your situation, please provide more details (i.e. what cells are you watching for changes that should update B2).

 

[reberryjr]

Thank you again! I "think" the concept is starting to sink in more. I have a larger, more complicated question that I posted. Once that gets resolved, I'll start on this one again. This one is dependent upon the other. Thank you very much for the links, and not just saying, "Here...do this.".

 

[Joe4]

You are welcome.

As you can see from the different links provided, there are different considerations to take into account, such as:
- Which cells should affect the date stamp (changes to one particular cell, certain cells, or any cell)?
- Do you only want to capture the initial date stamp, or should future changes update that date stamp?
- Are the changes you are looking for manual entries, or the results of formulas?

So there are many different things that will affect exactly how you will need to write the code.
When you get to the point of posting the question, the more details you can provide, the more likely you will get an answer that works for your precise situation (without having to make a bunch of modifications to it).

 

[reberryjr]

Ok, I think I will need some more help. The code below is "supposed" to run when the formula in "AW" changes the cell value to either "Paid" or "Late". The code worked yesterday, but it isn't working today. However, the piece that you were helping me with is still a struggle for me. The "=NOW()" did auto-change the date from 12/28/17 to 12/29/17 when I re-opened the workbook this morning. How can I adjust the code to NOT update the date?

Private Sub WorkSheetCalculate()

Application.ScreenUpdating = False
    
Dim ws As Worksheet
Dim LastRow As Long
    
For Each ws In Worksheets
    If Not ws.Name = "Bios" And Not ws.Name = "Stats" And Not ws.Name = "Financials" And Not ws.Name = "Variables" Then
    LastRow = ws.Range("AW" & ws.Rows.Count).End(xlUp).Row
        Select Case ws.Range("AW" & LastRow).Value
            Case "Paid", "Late"
                ws.Range("A" & LastRow + 1) = "=Today()"
                ws.Range("B" & LastRow + 1) = "=Now()"
                ws.Range("C" & LastRow + 1) = "Update"
                ws.Range("D" & LastRow & ":G" & LastRow).Copy ws.Range("D" & LastRow + 1)
                ws.Range("I" & LastRow & ":N" & LastRow).Copy ws.Range("I" & LastRow + 1)
                ws.Range("P" & LastRow & ":U" & LastRow).Copy ws.Range("P" & LastRow + 1)
                ws.Range("W" & LastRow & ":AB" & LastRow).Copy ws.Range("W" & LastRow + 1)
                ws.Range("AD" & LastRow & ":AI" & LastRow).Copy ws.Range("AD" & LastRow + 1)
                ws.Range("AK" & LastRow & ":AO" & LastRow).Copy ws.Range("AK" & LastRow + 1)
                ws.Range("AU" & LastRow & ":AW" & LastRow).Copy ws.Range("AU" & LastRow + 1)
        End Select
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[KevCarter]

It looks like you are running the code every time the sheet calculates, so the values will change daily.

I think you need to do another test to see if there is already a date entered, and only run the code when it's null. Something like:

if [COLOR=#333333]ws.Range("A" & LastRow + 1).Value = "" then

      run the update code  ...

End If[/COLOR]

You may even want to test for the value in one sheet before running the entire For Each ws In Worksheets loop...

 

[reberryjr]

The code should only run when the value of "AW" is changed to either "Paid" or "Late". When that happens, I do want the "=NOW()" to update.

 

[Joe4]

You are inserting a formula instead of a value. Formulas are dynamic.

Change this:

ws.Range("B" & LastRow + 1) = "=Now()"

to this:

ws.Range("B" & LastRow + 1) = Now()