IF Formula that inserts date

[g48dd]

Excel 2003: The value in column A will either be YES or NO, if it is YES I need column D same row to insert current dtae but that date needs to be static. I can write an IF Statement but I have no idea how phrase the "TRUE" part, or do I need VBA for this?

Thank you
Ken

 

[Jon von der Heyden]

Hi Ken

You can't write a formula to insert a constant date. We can do this using VBA, but please tell me, is this for one cell or multiple records (and what rows apply)?

 

[g48dd]

It's multiple records, 500 rows begins at A6. A6:A506 all read NO, Column B6:B506 has company phone numbers. I have to take inventory every month so I call 20 phones per day to verify location and user, then type in column A, "YES" when it has been verified. When I type YES I want the current date to appear in Column D. Static date. Column E adds 30 days to static date so that I know when I have to verify that number again. Column F subtracts the date in Column E from the date in Column D it shows days remaining to next verification date. That is a bit redundant but it serves a purpose. I want to eventually build a macro that when 1 occurs in Column F that on that event the sheet sorts it's self looking for all 1's, then sends Email to me automatically when the event occurs. I know how to do that part mmm or most of it. I know how to make the sheet send Email when an event occurs... doing the sort part I don't imagine is too difficult. That Email is my list of phone numbers that have to be called then next day. Normally I would not go to all this trouble but the phone list constantly changes so it is hard to stay up with it and that is part of my job, verify the inventory of phones, location and users every month. I know this is more info that you wanted but I thought it might help if you could understand what it is that I am doing. This sheet is open all day long on my desk, it is usually just minimized. So every morning it will update and send me the new list instead of me searching for all the numbers I must call that day. The list will change from month to month other wise I would just create a list for each day, but each month the lists are slightly different, that is why I am creating something that will help me stay up with what needs to be done and save me time. I am trying to save tax payers money by me being more efficient
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks<o></o>
Ken<o></o>
<o></o>

 

[Jon von der Heyden]

Right-click the sheet tab > view code. This will open the VBE.
Paste this into the code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Me.Range("A6:A506"), Target) Is Nothing Then
        If UCase$(Target.Text) = "YES" Then
            Target.Offset(, 3).Value = Now
        End If
    End If
End Sub

Close the VBE.
Changing any cell within A5:A506 to 'yes' will update the date in column D.

 

[g48dd]

Thank you, it works great.