How do I get today's date in a cell, only with text in anoth

January 26, 2016 at 00:27:20
Specs: Windows 7
I want to get today's date in a column of cells, but only when there is text in the adjacent cell.

message edited by Cabri

See More: How do I get todays date in a cell, only with text in anoth

Report •

January 26, 2016 at 00:33:36
This is a possible way, however there are some flaws with this, if you enter some text in A1 today, B1 will display todays date, however, when you open the workbook tomorrow it will change to tomorrows date. Not sure how to overcome this really, wait for some of the Excel Gurus they'll more than likely have a much better way than me :)

Put this in B1 and then type something in A1


Report •

January 26, 2016 at 03:23:45
The only way to lock in the date is with a macro. We need a little more info as to what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 26, 2016 at 03:31:47
Thanks! It works like a charm, I have however replaced the "TODAY" with a reference to another cell which contains the date of the day.
Any tips on creating a drop down menu containing names, numbers etc?

Report •

Related Solutions

January 26, 2016 at 03:41:11
You can create drop down using Data Validation. What i tend to do is

1) Create a sheet where i will have all my menu options
2) Create a list\column for each menu

So for example Sheet3 (you can call it MyMenuItems) or whatever you like

in Column A i will have for example

1 Hat
2 Coat
3 Scarf
4 Shoes

Then select all the items and to the left of the formula bar you will see a textbox, enter a name for this selection without spaces, for eample 'ClothingItems' press enter

Now go to the sheet where you want the drop down to appear, select the relevant cell, now go to the

Data tab
Select Data Validation
in the settings tab under 'Allow' select 'List'
in data enter =ClothingItems

press ok.

Now this cell will have the list you created.

You can hide that sheet and update as neccessary.

message edited by AlwaysWillingToLearn

Report •

January 26, 2016 at 11:01:27
To answer your original question:

I want to get today's date in a column of cells, but only when there is text in the adjacent cell.

There is a way, without the use of a Macro, try this:

First we have to set up the workbook so our magic will work.

Click the Microsoft Office button
Click on the Excel Options button to bring up the Excel Options dialog box (at the bottom of window)
Click on the Formula button in the left hand pane
In the right hand pane
Check the box next to: Enable iterative calculation
Change the number in the Maximum Iterations box to 1
Click OK

Now that the workbook is set up properly:

Cell B1 will contain your Current Date & Time, so enter this formula in cell B1


Now enter any Text or number in Cell A1 and Today's date should appear in cell B1.

Make sure you have cell B1 formatted as a Date Cell.

To change the date in cell B1, delete the text/number in call A1
then re-enter any text or number.

Excel considers a <SPACE> a valid character, so spacing over the text/number in A1 will leave the date in place.
This behavior can be an advantage, you don't need to Show anything in cell A1 or a disadvantage, depending on what your doing.

See how that works for you.


Report •

January 26, 2016 at 16:13:28
While the iterative calculation "magic" may work, it is not without it's drawbacks.

For one thing, you are essentially suppressing the warning about circular references. You will now be able to enter formulas that refer to themselves without realizing it. For example, this would normally present a Circular Reference warning:

L1: =K1
K1: =L1+M1

With iterative calculation enabled, you will not get any warning.

In addition, you have to be careful how you open/in what order you open workbooks or the option will be reset. The issue is explained here:

You will note that the "final solution" is to use a personal.xls file and set the option in that workbook. What that means is that you will suppress the circular reference warning for all workbooks. That may or not be an issue, but it is something that you need to be aware of.

Finally, it seems to me that the use of this option makes the workbook difficult to "share", and I don't only mean a "shared workbook" in the Excel sense, I also mean a workbook that might be placed on a network drive and opened by various users, even if individually. I think that the "reset option" problem will rear its ugly head unless everyone understands the issue and/or has a personal.xls file to force the option to remain set.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 26, 2016 at 17:27:10
I have to agree with all of what DerbyDad has to say, the solution does have its drawbacks.
Unfortunately there is no perfect solution for your request.


Report •

January 26, 2016 at 18:40:18
Wait a minute!

Who said macros aren't perfect? ;-)

message edited by DerbyDad03

Report •

Ask Question