On Jan 11, 2:03*pm, GS wrote:
LCTECH001 explained :

Can someone please help with the following? I have a blank cell in say M10
which when a date is typed into it is used in another formula which counts
all those entries before that date which are overdue. What I would like to
happen is if the cell is blank then todays date is automatically entered. I
have tried the following formula but all I get is the word FALSE and I need
todays Date. Can someone out there please assist?

=IF(ISBLANK(""),TODAY())

Thanks

You can't use a formula in M10 because it would be a circular reference
(refers to itself). What you need is a macro to enter today's date,
something like...

In a standard code module:

* Option Explicit

* Sub SetTodaysDate()
* * With Range("M10")
* * * If .Value = "" Then .Value = Date
* * End With
* End Sub

..which you could call from the Worksheet_Activate event...

In the code module behind the worksheet:

* Option Explicit

* Private Sub Worksheet_Activate()
* * Call SetTodaysDate
* End Sub

--OR--

..simply use the Worksheet_Activate event...

* Private Sub Worksheet_Activate()
* * With Range("M10")
* * * If .Value = "" Then .Value = Date
* * End With
* End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry,

Thank you for your quick response. I am unfortunately a bit of a
novice so just getting my head around excel formulas but I would
really like to give this a go! So far I've right clicked on the
worksheet and have copied your code in the code module behind the
worksheet but I'm now stumped what to do next?? I have been checking
out the internet on how to do this and its talking about how I have to
change security settings (i'm using Excel 2007) to medium etc etc
which is a whole new world to me and I would like to know more before
I get myself into more problems!!! Can you advise or is this going to
be a nightmare for you to explain!

Thanks in advance

L