Simple date validation

[DonTron]

I am trying to make a simple date validation so if the user leaves the specified data cell empty, it will automatically insert todays date in this format... "YYYY-MM-DD"

I tried a function like this:

Function NewDate(InputDate As Date) As Date
If InputDate = "" Then
NewDate = Now
End If
End Function

and then put this in the cell...

H46 =NewDate(H46)
(of cause I get the circle error thingy and cant insert date into the cell either etc. guess im a bit out of my league here)

Any tips on how to do this?

Thanks

[BrianMH]

Just use the data validation settings in excel and uncheck ignore blank that way if they click it they can't leave it blank. The reason you are getting a circular reference is because you are pointing the formula to the cell it is in. If you think about it your trying to make the formula over write itself.

You could put in it the formula =today()

Will this be on a read only spreadsheet? If so then when the close it the forumula will be =today() when opened again. Of course if they change and save it the cell will stay as whatever its been changed to.

It would be better if we had an idea of what your wanting to do with it.

Hope that helps.

[DonTron]

Thanks Brian, let me try and make the purpose a bit more clear.

I have a long table of cells like..
Name - Age - Phone - etc... - Last Updated

The Last updated is a date field where I want the user to either enter a date, or blank it to get "Now" automatically.

Example...


Row 1:
User enter... [2009-01-15]
Field remains unchanged [2009-01-15]

Row 2:
User enter... [ ]
Field is changed to [2009-02-24] (Now)

So I probably should not have the formular in all the Last Update cells.

Hope that makes it more clear.

[BrianMH]

Again your probably best to use data validation to require them to enter a value. If its just a time saver for them to enter todays date tell your users to use the keyboard shortcut ctrl + ;
ctrl + : does the time.

Is that helpful?

[DonTron]

Yes, I think I understand.

I put the function call up in the data validation instead of in the cell. That way I can manipulate what ever the user enteres. Is that how it works?

[mdmackillop]

You can also use a change event. In the attachment, any cell in columns A-E changed will put the date in Column G

[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 6 Then
With Cells(Target.Row, 7)
.Value = Date
.NumberFormat = "yyyy-mmmm-dd"
End With
End If
End Sub

[/VBA]

[BrianMH]

Data validation basically just forces someone to use data as you define it. So they have to use for example a date instead of some other type. You can also make it so it doesn't ignore blanks.

Of course mdmackillop's solution is better

[DonTron]

Thanks mdmackillop for the great solution.

It works perfectly