Free Microsoft Excel 2013 Quick Reference

Automatically insert date and time on same row that value is entered

I've created a list of call-ins. In column A is the Date and in column B is the time, I would like these cells to automatically populate based on the date and time information is entered in column D. I know to use a Worksheet_Change macro but I don't know how to indicate which row to populate the date and time into. My guess is that Excel knows which cell was just changed, if I could reference that I could offset or just goto column A and B of that row. I can't use current selection because some users may hit enter and some may hit tab. Does anyone know how I can reference the row of the cell that was just changed to execute the macro?

Post your answer or comment

comments powered by Disqus
I am planning to import data dirctly into excel via a tcp/ip port(winwedge or
every now and then a line of comma seperated text will be collected into a
row of cells in the spread sheet
is there any way of getting excel to add date and time information to each
entry, as the device that the data is being colected from has no realtime

I asked this question earlier today however, I didn't include enough info. I am keeping a repair log and want to include the date and time of repairs in that log. The formula I used in column A is =if(b1:b100="","",now()) and when you put anything in column b, the time and date is updated automatically, but this date and time is changed each entry or each time the doc is opened. I want to make the entry unchangable if possible.


I know the =now() function to show the current date and time on a spreadsheet. But I would like my graphs to show the current date and time (in the upper right corner) on them as well.

Is this possible?


I have Exel 2003 and 2007.

Ok I might be asking the obvious here but I'm dammed if I can find appropriate function

I am trying to automatically save date and time in spreadsheet only when spreadsheet is save, this is to keep a record on spreadsheet when it was last saved.

Could anyone help



I've made a macro that selects an entire row from one sheet and inserts into first empty row on another sheet. After I've done this I would like the first empry cell in the "new row" to contain the date and time of creation, but I can't seem to figure this out.

This is the code I'm currently using:
Sub Macro1()
' Macro1 Macro
    ActiveCell.Offset(1, 0).Select
    Do While Not IsEmpty(ActiveCell)
    ActiveCell.Offset(1, 0).Select
' This is where I want to find first empty cell in active row and insert current date and time
    Application.CutCopyMode = False
End Sub
Thank you!



I am using the following script for automatically enter date and time in column A on clicking. However, there is a small glitch. If u accidentally click on or move selection to an old cell, the date and time already entered is replaced by the recent time. This can't be undone. Is it possible that once date and time are entered, the cell gets locked so the data can't be changed even if u click on it once again. Other wise, is it possible to undo the entry. I don't want t oprotect the whole sheet with a password because i need to use the sheet every 2-3 minutes and enter date and time every time.
Many thanks
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("A:A")
Set IntersectRange = Intersect(Target, MyRange)
On Error GoTo SkipIt
If IntersectRange Is Nothing Then
Exit Sub

Application.ScreenUpdating = False
Target = Format(Now, "c")

End If

Exit Sub
End Sub

I have three columns (Date, Time, and Temperature) that I am trying to
graph as a scatter chart. I would like Date and Time together on the
x-axis and I would like Temperature on the y-axis. I can't get the
date and time combined on the x-axis. There are multiple measurements
on the same day and the measurements are not spaced evenly. I would
like the scale to be with a consistent unit (i.e. certain number of
hours between points). Any suggestions?


cdoughty's Profile:
View this thread:

I've got a spreadsheet that I am interested in calculating difference between times that span over two days. I'm trying to eliminate the amount of keystrokes and ensure data integrity so I am wanting to use the date and time picker in excel.

I've created a userform called userform1 placed a custom formatted date time picker control that I formatted it so that it shows date MM/dd/yyyy and time H:mm.

I need some help getting the selected date and time into the active cell, preferrably upon closing the userform.
I found some vba on ozgrid for the calendar control, but it doesn't seem to translate to the date/time picker.

In the userform I've inserted this code and when I call the userform via macro, I get this error "method or data member not found" and the debugger highlights Me.Calendar1 in the Userform_Activate procedure

Private Sub
ActiveCell = Calendar1.Value
    ActiveCell.NumberFormat = "mm/dd/yy"
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
Can someone help me mod this or provide some alternate code?


I have been all over the place trying to do this and I am completely lost.
Please help. I used the =Now function in excel to display the date and time
in a cell when another cell had information entered into it. This proved to
be nice until I realized that the adte and time was updating ANYTIME I made a
change to anywhere on the sheet. I need the date and time to remain static
after it appears in the active cell the first time.

After snooping around the net, it seems that the answer is in using a VBA
code which I have no inkling oon how to use, and the codes that were posted,
I wouldn't know how to change to meet my specific excel sheet. Also in the
instructions on the webpage said to right click on the sheet page and choose
to view code, which I did got a big window that I couldn't do ANYTHING in.
There was no place to put the code, and there are 3 items on the left, ther


When I try to open any of those, there is a password, exceot the VBAProject
which is unviewable.

What I need is simple if anyone would be kind enough to help me out. I need
I am guessing 2 codes.

I need the code to display the date and time in column E if data was entered
in the same row in column D. I also need the code to dispolay a date and time
in column G if information has been entered in the same row in Column F. The
date is dependent on information from only the same row and shouldn't be
affected by information entered on another row.

Please explain to me also what I am doing wrong that I am unable to enter
the code into excel when right clicking on the sheet and choosing to view
code. This would be much appreciated. Below is the code I found on the net
that I have no idea of the functionality of it. Thanks in advance for all who

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("B:B")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Add the Date in Column C whenever an entry is made in the same row in
Column B
If Target <> "" Then Target.Offset(, +1) = Date
' Clear the cell in Column C if the same row in Column B is cleared
If Target = "" Then Target.Offset(, +1) = ""
End Sub

I am trying to build a spreadsheet for logging in our fire hydrant tests. I am looking to write a funtion that will allow me to enter one letter or symbol in a cell and have the current date displayed. I will also need the same for time. I could also set the function to be contingent on a number value of a different cell. The function must keep the date and time static after entry. Any help would be greatly appreciated...... Mitch

Hello Experts,

I am facing one more problem in my master tracker, like when i entered date and time in Column C, Column D Automatically pop up the same date and time at same time. Are there any way to keep the column D blank unless i insert the date and time in column D.

Please find the attached file for your reference.

Note: Column C contains Received Date and Column D contains updated date and time.


I have three columns (Date, Time, and Temperature) that I am trying to graph as a scatter chart. I would like Date and Time together on the x-axis and I would like Temperature on the y-axis. I can't get the date and time combined on the x-axis. There are multiple measurements on the same day and the measurements are not spaced evenly. I would like the scale to be with a consistent unit (i.e. certain number of hours between points). Any suggestions?


When i enter data in column A, the date and times autofill in columns C and E. The problem is when I open sheet to add data in the next row, all previous dates and times revert to current date and time.
Kings Park3404/25/1104:13 PM2011042505565downWest Hempstead3804/25/1104:13 PM2011042509577down
column C formula is =IF(A9"",TEXT(TODAY(),"mm/dd/yy"),"")
column E formula is =IF(A9"",TEXT(NOW(),"hh:mm AM/PM"),"")

thank you

I need help with a macro that creates a chart on a newsheet. I want the title of the sheet have a name "Chart 1" and a function showing date and time

I tried something like this:

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _ 
"CHART 1. PF, EL, AND BM" & Application.Text(Now(), "mm dd yy  HH:mm:ss") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your time and assistance

Hi All,

I have a spreadsheet in which all of the toolbars are hidden and I would like to show a reasonably large Date and Time display on on of the sheets. I can show the time by using a Timer, but I don't know how to show the date. I don't want to just show the small time and date on the start bar at the bottom of the screen. Can anyone help me here please.

Thanks in advance for any help.



How do i enable a date and time function to appear on the worksheet - so it updates constantly (either minutes or seconds). If this isn't available can anyone tell me website that contains the UK date and time and updates it constantly, I have tried the web query with but for some reason the date doesn't come up.


I wonder if anyone can help me. I need to know how to create a command
button, when clicked it saves the spreadsheet along with the dates and time
on my desktop.

However, if that can't be there a formula that lets u saves the
spreadsheet along with the dates and time. Whenever I open the same spread
sheet, the dates gets changed and my previous information of the dates are
lost - I use [= NOW()]. How can i stop this?

i have a spreadsheet with hundreds of lines with a date and time in each row.
i want to show all data AFTER a specific date AND time.
for example. 'show all data after 2pm on the 20/12/2011'.
the data comes in a dd/mm/yyyy date format and hh:mm:ss time format.

please help! i been messing about with if statements for ages, and looked on the net for an answer.

thank you!

How do i do this? Problems with date and time...
On Duty Relived
a1 b1 c1 d1 e1 "answer"
01/11 13:00 01/12 02:30 13:30

I would like to keep the date's and time's in different cells for other
Also needs to work if the date is the same.

thanks for any help...


I need to plot a graph with temperature on Y axis and the date and time values on the X axis. However, the date needs to be shown only when it changes. Time needs to be shown at regular intervals, e,g. on the grid line. I need to use the scatter smooth line with nomarkers option of MS-Excel. I am using MS Excel 2003.

I also need to be able to zoom selected region in the graph and unzoom it.

Can anyone show me how to code with VBA for the above.
Thanks in advance.

The Date and Time Picker is not available in the Control Box

How can I insert a date and time into a column that does not change everytime I open or change the document. I use now() but the entire column changes, I want the cells to remain unchanged throuthout the document.


A1 9:00 (hrs production time needed)

B1 8:00 AM Start working hour
B2 5:00 PM End working hour

C1:C10 List of holidays

D1 02-01-2008 1:00 PM

E1 mm-dd-yyyy (Need to be calculated)

How can I calculate the estimated date and time to deliver the product using Excel? Taking in consideration that there are only 9 working hours per day.
Please help!


I have downloaded 5-minute-interval stock data from a data-vendor into CSV
format for display and charting in Excel. However, the date and time are in
UTC timestamp value (in 10 digits) which is the number of seconds elapsed
since 00:00 hours on Jan 1, 1970 UTC.

I need to convert them into Excel's date and time format. Would someone help
as I could not figure out how to do it properly with Excel functions? Your
help is greatly appreciated.

No luck finding an answer? You could always try Google.