Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
shorty
 
Posts: n/a
Default excel, automatic date and time when info gets entered

in excel, i am trying to get the date and time to automatically enter itself
when info is entered in another cell

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Celt
 
Posts: n/a
Default excel, automatic date and time when info gets entered


try this formula in the cell where you want the date and time to appear.
make sure the cell is formatted for Dates.

=if(-cell-<0,NOW(),"")

where -cell- is the cell reference of where you are entering your data.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=536454

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default excel, automatic date and time when info gets entered

Do you then want that date to be static?

Right-click on the sheet tab and "View Code".

Copy/paste the following event code to that module.

As you enter/edit data in column A, the date/time will be stamped in column B.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 26 Apr 2006 09:09:02 -0700, shorty
wrote:

in excel, i am trying to get the date and time to automatically enter itself
when info is entered in another cell


Gord Dibben MS Excel MVP
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
shorty
 
Posts: n/a
Default excel, automatic date and time when info gets entered

this works perfectly except that when I enter something in the cells it makes
the time the same for all the cells. I need every cell to have it's own
actual date and time.


"Celt" wrote:


try this formula in the cell where you want the date and time to appear.
make sure the cell is formatted for Dates.

=if(-cell-<0,NOW(),"")

where -cell- is the cell reference of where you are entering your data.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=536454


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Celt
 
Posts: n/a
Default excel, automatic date and time when info gets entered


yeah, my bad. I forgot that both NOW() and TODAY() update themselves
when the spreadsheet recalculates. Sorry about that.

Without doing it manually, a macro like Gord suggeted would be the only
way to get a static date.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=536454

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reflecting info between an excel file and a word one or two excel file. Starriol Excel Worksheet Functions 0 November 3rd 05 02:49 PM
Reflecting info between an excel file and a word one or two excel file. Starriol Links and Linking in Excel 0 November 3rd 05 02:49 PM
Reflecting info between an excel file and a word one or two excel file. Starriol Excel Discussion (Misc queries) 0 November 3rd 05 02:49 PM
How to Join/concatenate a date field with a time field in Excel? Alan Excel Discussion (Misc queries) 4 August 9th 05 10:07 PM
Imported Date & Time format with calcs. managed in excel from imrp Todd F. Excel Worksheet Functions 0 July 8th 05 09:03 PM


All times are GMT +1. The time now is 10:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"