Question on Date-Time format (SOLVED)

[Barry Katcher]

Hello, group.

If this line of code:

    ActiveCell.FormulaR1C1 = (Date)

returns this: 6/30/2003

and this line of code:

    ActiveCell.FormulaR1C1 = (Time)

returns this: 8:24:15 PM

then why doesn't this:

    ActiveCell.FormulaR1C1 = (Date)
    Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

return this: 6/30/2003 8:24:15 PM?

What it does return is 6/30/2003 12:00 AM.

Anyone have an idea what format I should be using to get the actual time and not have it default to 12:00? Thanx in advanx.

 

[TommyGun]

Re: Question on Date-Time format

Date returns only the Date stamp with no time information. Try using this...

ActiveCell = Now
Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"

 

[PaddyD]

Re: Question on Date-Time format

ActiveCell.FormulaR1C1 = (Now)


...cos date onl returns the integer portion (ie the date), not the decimal portion (ie the time) of now, the combined date & time

 

[Barry Katcher]

Re: Question on Date-Time format

Thanks, Tommy, it worked. I always thought NOW and TODAY returned volatile times, but this is static. Thanks again.

 

[TommyGun]

Re: Question on Date-Time format

Now and Time as formulas are volatile and will be recalculated each time with the spreadsheet. However, this code is simply hard coding the current system time into the activecell.

 

[Barry Katcher]

Re: Question on Date-Time format

Got it! Thanks for the clarification, Tommy.