Date Stamp Data entry

T

Truiz

Active Member
Joined
Jul 14, 2014
Messages
339
Good Morning,

I know this is an old topic that has been discussed many time but for some reason I haven't been able to find what I'm looking for which is simple I want excell to put the name of the month in Col. A and the date on Col. B (in the following format (20-Aug-2014) When I input data in Col G the use of the formula If combined with Now is not an option since it will change on a dalily basis and this is for a tracker so I need the dates to stay "Fixed" just to reflect when the data was input
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
When you say you want "Excel to put the name of the month ..." what exactly do you mean? Do you mean automatically? I think you will need to use a VBA macro of some sort if you want excel to put in a hard value based on someone putting information into another cell. Is that what you want?
 
Upvote 0
I don't know if this will help or not, but I did a Time Stamp macro a while back. The basics of the Macro were:

Select the Cell
Enter the formula =NOW()
Copy
Paste Special Values

Column B should have the Date format of dd - mmm - yyyy

I know this isn't the whole solution, but it's a start, perhaps?
 
Upvote 0
If you do want to go the Macro route then you don't need to even use the Now() then paste special. You can simply use the VBA value of "Now" when you run the code. Something like: Cell(x,y).value = Now. You can set the macro to run every time a worksheet changes, but be warned that the Undo functionality will loose its memory and be useless if the macro makes any changes to the sheet.
I would suggest a button on the sheet that the user could click right after inputting the data that would update the date fields you want.
 
Upvote 0
Try something like this:

Go to File > Options> Formulas

Check the Enable iterative calculation Box.

Set Maximum Iterations to 1
Set Maximum Change to 0.001

Then try a formula like this:
Excel 2012
AB
1Data EntryTimestamp
2

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B2=IF(A2<>"",IF(B2="",NOW(),B2),"")

<tbody>
</tbody>

<tbody>
</tbody>
Now when Data is entered in Cell A2 a timestamp will autofill in cell B2. Make sure column B is formatted to reflect the Time and Date values in the format you want.

The end result will be this:
Excel 2012
AB
1Data EntryTimestamp
2MM8/20/14 11:37 AM

<tbody>
</tbody>
Sheet1​
 
Last edited:
Upvote 0
Yes Automatically again as I mentioned when I type or paste something in Column G I want Column A to have the name of the month automatically and in Column B the date the data was inputed

mike72384 said:
When you say you want "Excel to put the name of the month ..." what exactly do you mean? Do you mean automatically? I think you will need to use a VBA macro of some sort if you want excel to put in a hard value based on someone putting information into another cell. Is that what you want?
Click to expand...
 
Upvote 0
That would be a good start yes that would work but insted of a macro having it done via an event code would be better as it will be done automatically

WLHagen said:
I don't know if this will help or not, but I did a Time Stamp macro a while back. The basics of the Macro were:

Select the Cell
Enter the formula =NOW()
Copy
Paste Special Values

Column B should have the Date format of dd - mmm - yyyy

I know this isn't the whole solution, but it's a start, perhaps?
Click to expand...
 
Upvote 0
Wouldn't the value change everytime there is a Re-Calculation?


mrmmickle1 said:
Try something like this:

Go to File > Options> Formulas

Check the Enable iterative calculation Box.

Set Maximum Iterations to 1
Set Maximum Change to 0.001

Then try a formula like this:
Excel 2012
AB
1Data EntryTimestamp
2

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B2=IF(A2<>"",IF(B2="",NOW(),B2),"")

<tbody>
</tbody>

<tbody>
</tbody>
Now when Data is entered in Cell A2 a timestamp will autofill in cell B2. Make sure column B is formatted to reflect the Time and Date values in the format you want.

The end result will be this:
Excel 2012
AB
1Data EntryTimestamp
2MM8/20/14 11:37 AM

<tbody>
</tbody>
Sheet1​
Click to expand...
 
Upvote 0
That's why the iteration is set to 1. It won't recalculate.

Note: It will recalculate when you delete the value in A2 and re-enter it.
 
Upvote 0
would that an effect on this formula that I have
Code: 
=IFERROR(VLOOKUP([@[Project Name]],Reference!$E$2:$F$63,2,FALSE),"")

mrmmickle1 said:
That's why the iteration is set to 1. It won't recalculate.

Note: It will recalculate when you delete the value in A2 and re-enter it.
Click to expand...

I've tried it and its not giving me the correct information depending on what I write it either gives me the same text string or if I put a number I get a weird date
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Copy data from a Data Entry form's table to the correct date in another tab
Replies
0
Views
72
Exceleration65
E
sakrams
  • Question
Excel VBA script for week number and the weekly dates (Concatenated)
Replies
8
Views
159
Cubist
C
D
  • Question
Updating multiple table entries by date with VBA
Replies
1
Views
196
NoSparks
N
D
  • Solved
Time stamp using VBA
Replies
5
Views
905
data808
D
B
  • Solved
Macro code changing date format
Replies
6
Views
106
Beneindias
B

Forum statistics

Threads
1,214,426
Messages
6,119,414
Members
448,895
Latest member
omarahmed1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top