insert date based on cell date

[Trevor3007]

good afternoon,

i am struggling to solve my issue & hope some 'code wizard' can solve?

My issue is , if cell a2 > 0, then insert todays date. So i need a formula for that and if I use Now() the date will change all applicable cells the following day . I need the original date to stay etc.

thank you for your help & for your time today.

KR
Trevor3007

 

[Joe4]

Personally, I prefer to use VBA to do this, as NOW() is constantly changing, and I am quite comfortable with VBA.
Here is a good thread that shows you all the different ways you can do it: https://excelchamps.com/blog/timestamp/

 

[Trevor3007]

hi,

thank you for your advise. I CANNOT use the Now() function as the dates will change all the previous dates to that date (IE 20/8/18 will now become 21/8/18 etc. I am happy to use VB but it must be so that when a2 is > than 0, b2 will enter the applicable date (the range is a2:a200 BTW) but would prefer a formula...

Again I thank you.

KR
Trevor3007

 

[Joe4]

Did you look at the link I provided? They show both VBA and non-VBA methods. The non-VBA methods make use of Circular Referencing.

The last method shows the VBA method.
However, if you would like it to run automatically when a value is manually entered into cell A2, you can use a Worksheet_Change event procedure instead, like shown here: https://www.mrexcel.com/forum/excel-questions/1009556-static-timestamp.html

So, the code to automatically update cell B2 with the current date/time if a value greater than 0 was entered into A2 would look something like:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 And Target.Address(0, 0) = "A2" Then
        If Target > 0 Then
            Target.Offset(0, 1) = Now()
        End If
    End If

End Sub

 

[Trevor3007]

Hi Joe4,

Thank you for your reply.

I had a look I found this:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) = "B57" Then
ActiveSheet.Unprotect "password"
If Target = "" Then
Range("B10").ClearContents
Else
Range("B10") = Now()
End If
ActiveSheet.Protect "password"
End If

End Sub​

however, this is the formula that is within the range a2:a200:-
=IF(B2>0,testdata!A2,"")



so in a lovely world, if a2 on the testdata sheet is > 0 , the applicable date will appear in b2.....

How do I tweak your code to reflect my formula?

MTA & KR
Trevor3007​

 

[Joe4]

I am confused here by your requirements.

You initially said:

if cell a2 > 0, then insert todays date

but you say that your formula is:

however, this is the formula that is within the range a2:a200:-
=IF(B2>0,testdata!A2,"")

So, it looks like you want to update column B if column A is greater than zero, but the formula you have in column A is looking to see if column B is greater than zero!
It seems you have a circular reference here!

Can you just explain (in plain English) exactly what is in columns A and B, and how they are supposed to interact?
There seems to be a discrepancy. Which one depends on the other?

 

[Trevor3007]

morning,

yes thanks to your keen eye, my formula is incorrect. it should be:-

=IF(testdata!G2>0,testdata!A2,"") , when this meets the criteria, b3 will insert the applicable date. I cannot use Now() as its used for statistics and would throw of the totals .

hope this now makes sense and thank you for your help :}

 

[Joe4]

I cannot use Now() as its used for statistics and would throw of the totals .

I am not sure what you mean by this. Do you mean that you only want the Date, and not Date & Time? If so, use Date instead of Now().
Note, that using Now() or Date in VBA is NOT necessarily the same as using those functions directly on the worksheets. By using it in VBA, you can return just the value, which "hard-codes" it into the cell, unlike using the function directly into the cell on the worksheet, which will be constantly changing.

Since that value is a formula, we will not be able to use a Worksheet_Change event on that sheet (which only captures manual changes, not changes due to a formula).
The question is, how/when do you want this VBA code to run? Do you want to run it manually?

If you want it to run automatically, we have to have some event trigger it.
Are you manually updating the values in column G on the "testdata" sheet? If so, we can trigger it off of a manual change to that sheet.

 

[Trevor3007]

Hi joe4,

many thanks again for your help. I wil do my best to simplify. In order for the date to be inserted in the range b2:b200 (worksheet named results) it runs off this formula:
=IF(testdata!G2>0,testdata!A2,"") , this is in the range a2:a200

(worksheet named results)

I want it to run automatically so formula or VBA I am not bothered :} just as long as the date/time is applicable to that day (hence why I CANNOT use Now() otherwise it will mess up the daily/weekly stats.

I thought the formula would be

=IF(testdata!G2>0,testdata!A2,"",=date())
but obviously its not :{ hence why after surfing the web I had to request help via Mr Excel .

I hope this has helped you to give a better insight?

KR
Trevor3007​

​

 

[Joe4]

I want it to run automatically so formula or VBA I am not bothered :} just as long as the date/time is applicable to that day (hence why I CANNOT use Now() otherwise it will mess up the daily/weekly stats.

I don't think you are understanding the clarification I am making here.

Note, that using Now() or Date in VBA is NOT necessarily the same as using those functions directly on the worksheets. By using it in VBA, you can return just the value, which "hard-codes" it into the cell, unlike using the function directly into the cell on the worksheet, which will be constantly changing.

Putting:
=NOW()
in a cell returns the current date/time in any cell. But the issue, as you see, is that it always returns the current date/time, so it is constantly changing (it is not static).

However, doing something like this in VBA:

Sub Test()
    Range("A1") = Now()
End Sub

puts a permanent date/time stamp in cell A1 (that does NOT change; it is hard-coded).
So VBA is probably the route we want to go.

I want it to run automatically

OK, now that we have that established, we need to determine what exactly is going to trigger the VBA code to run. Typically, it is on events like:
- the opening of the file
- the change in the value of particular cells

I am thinking that you are probably leaning towards the second option listed above, but it needs to be defined more clearly, exactly:
- what is changing
- how is it changing
- when it it changing

If it is a manual change directly to a cell, that is pretty easy to do. However, your cells are formulas, not hard-coded values that are being changed.
We need to look at how the data being returned by those formulas is changing. It looks like the formulas are using values from columns A and G on your testdata sheet.So, please answer the following questions:
- What is in columns A and G on your testdata sheet (more formulas or hard-coded values)?
- How are those values in columns A and G on your testdata sheet being changed?
- When are those values in columns A and G on your testdata sheet being changed?

Can you answer those three questions?

Also, another question that we need answered is the following:
- Is this date/time that you want updated ONLY in row 2, or are there multiple rows of data?
- If there are multiple rows of data, is the data from row 3 pulling from row 3 of your testdata sheet, and row 4 pulling from row 4 of your testdata sheet, etc?