Excel - Date & Time Stamp

Hi,

I would like to know if there is a way to set up when I enter the data
each cells on first row, it will default the date and time stamp on
first row. 2nd row should have the different date and time stamp than
1st row.

I put "=now()" but it create the same date and time for 80 rows. I
don't want that. I want to enter each rows and each rows should be
different by one minute apart but same date - June 22.

Your help would be much appreciated.

Thanks

Format A1:A79 as Custom dd-mmm-yy h:mm

Select A1

Hold down CTRL Key and hit semi-colon key then <space> bar.

Still holding CTRL key hit SHIFT key and semi-colon key.

Select A2 and enter =A1 + 1/1440

Drag/copy down to A79


Gord Dibben MS Excel MVP

On 22 Jun 2006 16:10:13 -0700, "learning_codes@hotmail.com"
<learning_codes@hotmail.com> wrote:

>Hi,
>
>I would like to know if there is a way to set up when I enter the data
>each cells on first row, it will default the date and time stamp on
>first row. 2nd row should have the different date and time stamp than
>1st row.
>
>I put "=now()" but it create the same date and time for 80 rows. I
>don't want that. I want to enter each rows and each rows should be
>different by one minute apart but same date - June 22.
>
>Your help would be much appreciated.
>
>Thanks

Thanks for the information. I tried and it works.

I need to know if there is a trigger when I enter "Title" in A5 and the
code create the date stamp on A1.

Will that be possible ??

Thanks

Gord Dibben wrote:
> Format A1:A79 as Custom dd-mmm-yy h:mm
>
> Select A1
>
> Hold down CTRL Key and hit semi-colon key then <space> bar.
>
> Still holding CTRL key hit SHIFT key and semi-colon key.
>
> Select A2 and enter =A1 + 1/1440
>
> Drag/copy down to A79
>
>
> Gord Dibben MS Excel MVP
>
> On 22 Jun 2006 16:10:13 -0700, "learning_codes@hotmail.com"
> <learning_codes@hotmail.com> wrote:
>
> >Hi,
> >
> >I would like to know if there is a way to set up when I enter the data
> >each cells on first row, it will default the date and time stamp on
> >first row. 2nd row should have the different date and time stamp than
> >1st row.
> >
> >I put "=now()" but it create the same date and time for 80 rows. I
> >don't want that. I want to enter each rows and each rows should be
> >different by one minute apart but same date - June 22.
> >
> >Your help would be much appreciated.
> >
> >Thanks

You will need event code.

But if you use A5 as a trigger cell how can you fill A1:A79 with date/time as
first desired?

Maybe use a cell in Column B as a trigger cell.

Try this code in your worksheet.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanUp
Application.EnableEvents = False
If Target.Address = "$B$1" Then
If Target.Value = "Title" Then
Range("A1").Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End If
CleanUp:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.


Gord

On 22 Jun 2006 18:56:55 -0700, "learning_codes@hotmail.com"
<learning_codes@hotmail.com> wrote:

>Thanks for the information. I tried and it works.
>
>I need to know if there is a trigger when I enter "Title" in A5 and the
>code create the date stamp on A1.
>
>Will that be possible ??
>
>Thanks
>
>Gord Dibben wrote:
>> Format A1:A79 as Custom dd-mmm-yy h:mm
>>
>> Select A1
>>
>> Hold down CTRL Key and hit semi-colon key then <space> bar.
>>
>> Still holding CTRL key hit SHIFT key and semi-colon key.
>>
>> Select A2 and enter =A1 + 1/1440
>>
>> Drag/copy down to A79
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On 22 Jun 2006 16:10:13 -0700, "learning_codes@hotmail.com"
>> <learning_codes@hotmail.com> wrote:
>>
>> >Hi,
>> >
>> >I would like to know if there is a way to set up when I enter the data
>> >each cells on first row, it will default the date and time stamp on
>> >first row. 2nd row should have the different date and time stamp than
>> >1st row.
>> >
>> >I put "=now()" but it create the same date and time for 80 rows. I
>> >don't want that. I want to enter each rows and each rows should be
>> >different by one minute apart but same date - June 22.
>> >
>> >Your help would be much appreciated.
>> >
>> >Thanks