If 'Yes' and insert date query

[Iain McBride]

Hi - I am trying to insert a date into my excel spreadsheet when an answer is yes.

However, the formula I am using, =IF(B6="Yes",TODAY(),""), always gives me the current date rather than the date that the information was input.

Any idea what's the right formula?

Thanks
Iain

 

[philwojo]

The TODAY() function is designed to return the current system date, so do you have another cell that you store the date the information was input on your sheet? if not how would the system know when the data was input?

 

[mumps]

As far as I know, you can do what you want only using a macro.

Sub test()
    If Range("B6") = "Yes" Then
        Range("A1") = Date
    End If
End Sub

 

[Iain McBride]

That's a fair point - my knowledge about excel and how it works is limited - would a macro work better in this case?!

The question is only simple - Have you completed this form? Yes - and date it was completed - how it could be any date of the year so nothing for the TODAY to link to I guess?!

 

[mumps]

You could have the macro prompt you with "Have you completed this form?" and if you answer "Yes" you would be asked for a date and the date would be inserted. In which cell do you want to insert the date? You could even have a calendar pop up where you could choose any date.

 

[philwojo]

I think I misunderstood what you wanted when I first read your post, sorry about that, but I think Mumps is right, I am not aware of a way to do it without the macro (vba) that he provided.

It might just be easier to have the person input the date in addition to answering Yes, but that is just my opinion.

 

[Iain McBride]

Thank you gents - I will attempt the vba - and then if that goes wrong they can do it the long way round haha..
Thanks you both though

 

[gallen]

You can 'trick it' using circular references... though it falls down if the user deletes the original value.

I got this when I googled it. I thought I'd used it before but a long time ago so couldn't remember how.

https://www.google.co.uk/search?q=t....1.69i57j0.11221j0j7&sourceid=chrome&ie=UTF-8

 

[mumps]

Click here to download a sample file. When you click in any cell in column A, you will be asked your question. If you click "Yes", a calendar will pop up where you can select a date. When you select a date, it will be entered into the clicked cell. The macro which does this action will have to be modified to include the range where you actually want the date added. You can do this by right clicking the tab name for Sheet1 and then clicking 'View Code'. A window will open to make the macro visible. Change the Range("A:A") to the desired range and close the code window. Click on any cell in the desired range.