General Question

DGradess's avatar

How do I automatically add the date to a MS Excel cell?

Asked by DGradess (11 points ) August 17th, 2009

I’m running a phone bank and a I want every record of an interaction to have a date before it, and it would be neat if Excel did it automatically. For example, I want to enter into a cell, “called left message,” hit enter,” and have the result be, “8–17-09: called left message.” Any help?

Observing members: 0 Composing members: 0

5 Answers

se_ven's avatar

You would want a macro, or if you hit Ctrl+; it automatically puts in the current date in the selected cell, and you can add the called left message afterward…

If you want to do a macro, you can record one of you selecting a cell and hitting Ctrl+; and that would give you most of the macro. You would then need to edit the macro to work for each line. So it might be easier to use the Ctrl+; shortcut

Response moderated
PerryDolia's avatar

To automatically enter the date and time:

Select a cell and press CTRL+; then SPACE then CTRL+SHIFT+;

graynett's avatar

Just to make it clear “ctrl +” is Insert, so “ctrl” and ”;” does the trick

prasad's avatar

To do this, you’ll need a macro. I’ve tried one and you can copy-paste it into your VBA editor. Follow these steps:

1. Open your excel file or a new excel file. Open the VBA editor (press Alt + F11).
2. You’ll find in the left pane “Project Explorer”. If you don’t see it, make it from menu: View-> Project Explorer (Ctrl + R).
3. Now, decide how you want this to happen. I mean, you can do this to a particular sheet or all sheets in your workbook.

i) For a particular worksheet (1 worksheet):
Double click the sheet, that you’re interested in, in the project explorer (step 2). A code window will appear to the right. Copy paste the code below into it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Date_Today As String, Place_Date As String
Date_Today = Format(Now, “mm/dd/yyyy”) & ”: ”
Place_Date = ”””” & Date_Today & ”””” & ”#”
Cells(1, 1).NumberFormat = Place_Date
End Sub

ii) For all worksheets in the workbook:
Double click “ThisWorkbook” in project explorer. A code window will appear. Copy paste the following code into it:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Date_Today As String, Place_Date As String
Date_Today = Format(Now, “mm/dd/yyyy”) & ”: ”
Place_Date = ”””” & Date_Today & ”””” & ”#”
Cells(1, 1).NumberFormat = Place_Date
End Sub

4. Now, the code has been placed. Test it. Go in your worksheet, type anything in a cell and hit enter. Today’s date will be added to what you just entered into the cell.

Actually, even if you see date in the cell, you won’t find it in the formula bar. I have just changed the format of the cell that you enter in to a custom format. In custom format (right click a cell and select format cells), you can show anything you want in addition to a value entered in. Place your text in double quotes and type # and click OK. And, see it yourself. If you drop #, only the text will be shown irrespective of what value is entered in that cell.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther