Insert Current Date & Time in a Single Cell

[animas]

I need a macro/vba code which will insert current date and time inside a single cell as the following format:
dd/mm/yyyy hh:mm AM/PM (i.e. 01/10/2010 05:34:00 PM)

How to do that?

Thanks.

 

[JamesW]

In the cell put this:

=NOW()

And format it to:

dd/mm/yyyy hh:mm AM/PM

 

[Ruddles]

Do you need it to change when the sheet is recalculated or do you need it to be fixed to the date/time it was entered?

If the latter: Ctrl-; {space} Ctrl-Shift-:

Or do you want actual VBA code?

 

[animas]

do you need it to be fixed to the date/time it was entered?

If the latter: Ctrl-; {space} Ctrl-Shift-:

Or do you want actual VBA code?

VBA code for later.

 

[Ruddles]

Format(Now(), "dd/mm/yyyy hh:nn AM/PM")

or:-

Format(Now(), "dd/mm/yyyy hh:nn:ss AM/PM")

 

[animas]

thanks for the nice solution.

 

[hstaubyn]

How about for when you want to combine a date and time that is not given by the formula Now()? Can you do something like

Format(myDate & myTime, "dd/mm/yyy hh:mm:ss AM/PM")

?

 

[Ruddles]

How about for when you want to combine a date and time that is not given by the formula Now()? Can you do something like

Format(myDate & myTime, "dd/mm/yyy hh:mm:ss AM/PM")

?

If myDate and myTime are Dimmed as Date, then you'd probably add them using + rather than concatenate them with &.

The Format function must have a date type variable fed to it as the first argument.

 

[KPM171]

I need a macro/vba code which will insert current date and time inside a single cell as the following format:
dd/mm/yyyy hh:mm AM/PM (i.e. 01/10/2010 05:34:00 PM)

How to do that?

Thanks.

Drop this into your macro where "A1" is the cell where you want to display the date/time stamp.

With Range("A1")
.Value = Now()
.NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
End With

 

[HighAndWilder]

I have changed the way in which the date and time is written to the worksheet. This is sample data to be used in a generic data extraction and
normalisation userform project.

I originally created the data and applied it to each cell in turn in one line which had two random number generations in it for the date offset and time of day.

This line also formatted the data.

I have now split it into four lines to generate the date, time, write the data to each cell and then format the data.

I'm not normally one for writing code taking up as few lines as possible and would rather keep it simple and easier to understand.

Thanks for your help.