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

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

The code will run whenever the sheet is activated. If the workbook is
saved with that sheet the active sheet then the code will run when the
file opens. Not, though, tht it will also run if you switch back to
that sheet after working on another sheet, OR working in another
workbook and switching back to that sheet.

Once you put macros into a workbook, you'll need to change Excel's
Macro Security to use the macros whenever the workbook is reopened.
Everyone that uses this file will have to do the same.

In Excel2007...

Home button Excel Options Trust Center Trust Center Settings...

In the Trust Center dialog...

Macro Settings
..choose option2 under 'Macro Settings' section

...which will disable macros with notification that prompts you to
enable them via a warning bar at the top of the worksheet. Note that
leaving macros disabled will require making the changes manually, same
as without the macro.

HTH

--
Garry

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

GS has a motor skills disability that causes him to miss keystrokes
when he's tired. Geez, I hate when that happens!

The code will run whenever the sheet is activated. If the workbook is saved
with that sheet the active sheet then the code will run when the file opens.

Note, though, that it will also run if you switch back to that sheet
after

working on another sheet, OR working in another workbook and switching back
to that sheet.

Once you put macros into a workbook, you'll need to change Excel's Macro
Security to use the macros whenever the workbook is reopened. Everyone that
uses this file will have to do the same.

In Excel2007...

Home button Excel Options Trust Center Trust Center Settings...

In the Trust Center dialog...

Macro Settings
..choose option2 under 'Macro Settings' section

..which will disable macros with notification that prompts you to enable them
via a warning bar at the top of the worksheet. Note that leaving macros
disabled will require making the changes manually, same as without the macro.

HTH

--
Garry

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

You should re-think this statement GS

If the workbook is saved with that sheet the active sheet then the code will run when the
file opens

Code will run only when the sheet is activated.

Opening a workbook does not activate a sheet.



Gord

Gord Dibben brought next idea :
You should re-think this statement GS

If the workbook is saved with that sheet the active sheet then the code will
run when the file opens

Code will run only when the sheet is activated.

Opening a workbook does not activate a sheet.



Gord

Yes, you're absolutely right! Hmm.., and so I will rethink this...

--
Garry

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

Scrap all the previous code posted, and delete it ALL from your
workbook!

In the VBE, double-click ThisWorkbook and paste the following into the
code window:

Option Explicit

Private Sub Workbook_Open()
With Sheets("<sheetname").Range("M10")
If .Value = "" Then .Value = Date
End With
End Sub

**Be sure to substitute <sheetname with the actual sheet name!

--
Garry

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

On Jan 12, 12:29*pm, GS wrote:
Scrap all the previous code posted, and delete it ALL from your
workbook!

In the VBE, double-click ThisWorkbook and paste the following into the
code window:

Option Explicit

Private Sub Workbook_Open()
* With Sheets("<sheetname").Range("M10")
* *If.Value = "" Then .Value =Date
* End With
End Sub

**Be sure to substitute *<sheetname with the actual sheet name!

--
Garry

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

Hi Garry,

Thank you for assisting with this! I have etered this new code into
the VBE Code sheet but nothing is happening! When I copy and paste the
code in I have pressed the save icon but I'm not sure if I have to
save the code as a macro? Do I name it to run it in the actual
worksheet as I have deleted the formula I had in M10 and now the cell
is blank and todays date is not appearing! Can you advise what I'm
doing wrong?

Best Regards

LC

LCTECH001 laid this down on his screen :
On Jan 12, 12:29*pm, GS wrote:
Scrap all the previous code posted, and delete it ALL from your
workbook!

In the VBE, double-click ThisWorkbook and paste the following into the
code window:

Option Explicit

Private Sub Workbook_Open()
* With Sheets("<sheetname").Range("M10")
* *If.Value = "" Then .Value =Date
* End With
End Sub

**Be sure to substitute *<sheetname with the actual sheet name!

--
Garry

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

Hi Garry,

Thank you for assisting with this! I have etered this new code into
the VBE Code sheet but nothing is happening! When I copy and paste the
code in I have pressed the save icon but I'm not sure if I have to
save the code as a macro? Do I name it to run it in the actual
worksheet as I have deleted the formula I had in M10 and now the cell
is blank and todays date is not appearing! Can you advise what I'm
doing wrong?

Best Regards

LC

The code goes in the code window behind ThisWorkbook. It will run every
time the workbook is opened.

To access the VBE keyboard Ctrl+F11 and then expand the 'Microsoft
Excel Objects' folder. Double-click on 'ThisWorkbook' and paste the
code.

***Make sure you edit <sheetname to match the name of the sheet you
want this to affect***

**Remove this code if you put it anywhere other than in ThisWorkbook**

--
Garry

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

Excel Formula for Entering Today's Date if Cell is Blank

Here's the corrected formula:

This formula checks if cell M10 is blank. If it is, it enters today's date using the TODAY() function. If it's not blank, it simply returns the value that's already in the cell.

To use this formula, simply copy and paste it into the cell where you want the date to appear (in this case, M10). Once you've done that, the cell will automatically update with today's date if it's left blank.

1. Copy and paste the formula into the cell where you want the date to appear (in this case, M10).
2. The cell will automatically update with today's date if it's left blank.

I hope that helps!