Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Is it possible to have function which can insert date automatically in Excel

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 25-02-2012
Member
 
Join Date: Feb 2012
Posts: 13
Is it possible to have function which can insert date automatically in Excel

Does anyone know of a function that can make Excel automatically insert the current date into a cell when a file is opened up.

Reply With Quote
  #2  
Old 25-02-2012
Member
 
Join Date: Aug 2011
Posts: 564
Re: Is it possible to have function which can insert date automatically in Excel

You could put =TODAY() in a cell and it will up date, if you don't want the date to change after you put it in use some code in the workbook open event to do it, like this : Sheets("Sheet1").Range("A1") = Date.
Reply With Quote
  #3  
Old 25-02-2012
Member
 
Join Date: Jul 2011
Posts: 618
Re: Is it possible to have function which can insert date automatically in Excel

I have a question about inserting a current date into a spreadsheet, but I don't want the date to change once I've saved it, closed it and reopened it on a different date. Can you explain the formula to insert the current date in a cell (or range of cells) and once you save, that date stays, but the next day enters that current date.
Reply With Quote
  #4  
Old 25-02-2012
Member
 
Join Date: Jul 2011
Posts: 631
Re: Is it possible to have function which can insert date automatically in Excel

While neither a formula solution nor an automated method, simply doing Ctrl+; (in other words, holding down the Ctrl key while hitting the semi-colon) will enter the current date as a static (non-changing) date into the active cell.
Reply With Quote
  #5  
Old 25-02-2012
Member
 
Join Date: Jul 2011
Posts: 632
Re: Is it possible to have function which can insert date automatically in Excel

How can I make today's date auto fill in a cell upon entering data in another cell. I would like the cell B1 that contains the date to remain empty until I enter data in cell A1.
Reply With Quote
  #6  
Old 25-02-2012
Member
 
Join Date: Jun 2011
Posts: 632
Re: Is it possible to have function which can insert date automatically in Excel

An easy way is to make cell B1 have =today() be in it, but format it to white text. then do conditional formatting that if A1<>"" then the text changes to black and the date can be seen. that way the date is always there, you just don't see it until you enter data into cell A1. hope that helps.
Reply With Quote
  #7  
Old 25-02-2012
Member
 
Join Date: Jun 2011
Posts: 486
Re: Is it possible to have function which can insert date automatically in Excel

I am assuming that once the date is added to the worksheet, you would not want it to change. If that is the case, you will need to use VB event code to handle this. Is a VB solution an acceptable choice.
Reply With Quote
  #8  
Old 25-02-2012
Member
 
Join Date: Jun 2011
Posts: 798
Re: Is it possible to have function which can insert date automatically in Excel

I know VERY LITTLE about using Excel -- just learning -- so trial and error is my only option. I have Excel X for Mac (a slightly older version). I want to cause the date inside one of the cells to change automatically when I open. I tried adding =TODAY() directly into the cell, but it only prints with those same characters in the document. It doesn't show a date. I went into "View Code" to try adding it there, but there is already stuff in the window. I don't really understand how to do this. Can you help me.
Reply With Quote
  #9  
Old 25-02-2012
Member
 
Join Date: May 2011
Posts: 446
Re: Is it possible to have function which can insert date automatically in Excel

Format that cell as General or with the specific date format that you prefer, then re-enter the formula. The cell was formatted as text, which disables formulas just for that cell. Or else try the below code :
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("D")) Is Nothing Then
Target.Offset(0, -3).Value = Format(Now, "mm dd yyyy h:mm:ss")
End If
If Not Application.Intersect(Target, Columns("I:I")) Is Nothing Then
Target.Offset(0, 1).Value = Format(Now, "mm dd yyyy h:mm:ss")
End If
End Sub
Reply With Quote
  #10  
Old 25-02-2012
Member
 
Join Date: May 2011
Posts: 410
Re: Is it possible to have function which can insert date automatically in Excel

Copy/paste this code to your sheet module.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yyyy hh:mm")
End If
stoppit:
Application.EnableEvents = True
End Sub

If you want this for any cell in Column A use this code instead.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Me.Range("A" & n).Value <> "" Then
Me.Range("B" & n).Value = Format(Now, "mm-dd-yyyy hh:mm")
End If
End If
stoppitl:
Application.EnableEvents = True
End Sub
Reply With Quote
  #11  
Old 25-02-2012
Member
 
Join Date: Feb 2012
Posts: 13
Re: Is it possible to have function which can insert date automatically in Excel

How can I insert a date which is the first date of the following month of a given date. For example, I have a number of dates and I need to put the 1st date of the following month. Can I do it using excel formula. or will I have to inset the 1st date of the following month manually.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Is it possible to have function which can insert date automatically in Excel"
Thread Thread Starter Forum Replies Last Post
Function to automatically list in excel sheet Rajani^kanta Windows Software 3 01-02-2011 05:57 PM
Way to insert a Date Picker in MS Excel cell Chulbul Pandey Windows Software 3 11-12-2010 12:55 AM
How to insert pictures into Excel automatically ? Metaldigger Software Development 7 10-03-2010 12:32 AM
How to insert last date in excel 2010 Calum Windows Software 5 18-02-2010 12:07 AM
Excel's Time Capture ("Insert a static date or time") Function: Is it possible to have it capture times down to the second? oogoog747 Windows Software 2 10-04-2009 09:36 AM


All times are GMT +5.5. The time now is 04:25 PM.