Use date document created in formula?

[jamieuk]

We have a template that requires the user to enter the date they
started using the workbook. This date is used in many formulas within
the template.

Rather than requiring the user to enter this date, it would be nice to
be able to derive this information, for example, using the date the
workbook was created. The stipulation is, however, the user should be
able to use the workbook with macros disabled i.e. we don't want a VBA
solution.

Is this possible?

Many thanks,
Jamie.

 

[Bernie Deitrick]

Jamie,

You could set a conditional format linked to the value of the date
cell - if it is empty, most cells could be red, for example, with one
cell that is colored green, is formatted for a large font, and has a
formula like:

=IF(A1="","Enter a date in cell A1","")

HTH,
Bernie
MS Excel MVP

 

[jamieuk]

Thanks, Bernie. But I don't want the *user* to enter the date cell
value. Instead, I want to derive the date.

For example, I could do this:

Private Sub Workbook_Open()

With ThisWorkbook.Worksheets("Main").Range("StartDate")

' test for start date
If CLng(.Value) = 0 Then
.Value = CLng(Now)
End If

End With

End Sub

....HOWEVER I want a solution that will work with macros disabled.

Is there a way of doing the same without using VBA?

 

[Bernie Deitrick]

Jamie,

"> Is there a way of doing the same without using VBA?

No.

The best you can do is use conditional formatting to draw the user's
attention to the fact that there is data missing.

HTH,
Bernie
MS Excel MVP