convert yes to date

[jvlavl]

Hello,

I'm looking for a way to convert the YES input to the today date

for example A3 is filled with Yes on Sheet1 sheet2 A4 should be filled with the Date of today.

Is there a way to do this?

If yes could you help me on the way?

Thanks in advance

Rgds,

John

 

[mumps]

Will you always only be working with A3 in Sheet1 and A4 in Sheet2?

 

[jvlavl]

Hello,

Yes this fields will always be the same on each row

Rgds

John

 

[mumps]

Make sure that A4 in Sheet2 is formatted as "Date" and then copy/paste this formula in A4: =IF(Sheet1!A3="Yes",NOW(),"")

 

[jvlavl]

Hello,

Thanks for answer, but I how do I include this in a Macro that runs when the file is closed?

Rgds,

John

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. When you close the workbook, the macro will run automatically.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Sheets("Sheet1").Range("A3") = "Yes" Then
        Sheets("Sheet1").Range("A3") = Date
    End If
End Sub

If you also want the macro to run when you save the workbook, add this macro to the same code module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets("Sheet1").Range("A3") = "Yes" Then
        Sheets("Sheet1").Range("A3") = Date
    End If
End Sub

 

[jvlavl]

Thanks that solved my question

 

[mumps]

You are very welcome.