Setting Current Date via a button

[DrakeFS]

I am Using an If statement to check if something has been verfied in the last 30 days.

=IF(TODAY()<=DATE(2011, 9,27)+30,"Yes","No")

Now I would like to create a button that modifies the Date(y,m,d) field to current date. Anyone have a clue how I could set the y, m, and d to the current values using a button in excel?

 

[Scott Huish]

The resulting formula would make no sense then.

TODAY will always be less than TODAY + 30.

What are you trying to do?

 

[DrakeFS]

it actually works, if date was set to 2011, 8,27 it out puts no (which was 30 days away) The only thing it does is check to see if the date (which is static right now) is is greater than 30 days past.

The button should change to static date to the current date (still a static value) and allow me to click it to set the answer to yes every 30 days


I am not trying to set the Date() to Today() in the formula, I am trying to set the vaule of Date() to the current Date (it should remain static in the formula)

a VBA script is what I assume I will need to do this.

 

[Scott Huish]

I used cell A1, change as necessary:

Private Sub CommandButton1_Click()
Dim yr As Long, mh As Long, dy As Long, t As Long
t = Date
yr = Year(t): mh = Month(t): dy = Day(t)
Range("A1").Formula = "=IF(TODAY()<=DATE(" & yr & "," & mh & "," & dy & ")+30,""Yes"",""No"")"
End Sub

 

[DrakeFS]

Thank you very much