Inserting the current date automatically in a cell when filling in a data sheet

[Andyk8ley]

I need to have a date cell (D2) automatically filled in with the date on which that row was completed. If the data capturer filles in the client details in another cell (F2), I would like to have the date permanently input into cell D2.

Please assist

NUM	CONFIRMATION AGENT INITIALS	PLEDGE AGENT INITIALS	INITIAL DATE DATE YYMMDD	LANGUAGE	PHONE (CELL)
1		LR	150401	English	0833836255

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

Thanks

 

[Andyk8ley]

Ok. if I copy paste it as values, then it comes out in the correct format???

If I format column D as TEXT, then it is correct. Now I am confused, maybe makes sense to you

I don't know why it would do that. 'Date' returns the current date in system date format, so the code should return 150524 for todays date. It does that on my system.

 

[MARK858]

Makes sense the number which is in the cell is a number not a date so if you try formatting it as a date you will get 13th February 2312 (or 120213 in yymmdd format) with the number 150524.

 

[JLGWhiz]

The Format(Date, "yymmdd") produces a string value. As MARK58 says, it needs to go to a cell whose number format is either General or text. It cannot be formatted as "Date" or Excel will take the literal value of the number and convert that to a Date format. Dates can be a pain in the rear, unless you understand how Excel treats them.