Not for me it didn't, I either had to format the cell or coerce it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob van Gelder" wrote in message
...
Excel will still treat the string as a date, so you get the same result.
Better effort spent formatting the cell to dd mmm yyyy

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
You still need to format the result

Range("A1").Value = Format(PayDate, "dd mmm yyyy")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob van Gelder" wrote in
message
...
Datatype conversion is where a lot of bugs occur.

In your case, you're handing the responsibility of datatype conversion
to
Excel in two places:
The Format function returns a string, which you are trying to assign to
a
date.
Application.InputBox returns a string, which you are trying to assign
to
a
date.

When you let the compiler (Excel) do the datatype conversion for you,
you
had better be sure you know how Excel is going to do it.


Simple answer to your question:

DateFormat = Now()

PayDate = Application.InputBox("Input Pay Date to appear on
Payslips",
"DATE REQUIRED", Format(DateFormat, "dd mmm yyyy"))


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"TroyB" wrote in message
...
Hi,

I have the following extract of code requiring the user to input the
date
into a prompt and then inserted into the spreadsheet.



Sub DateTest()

dateformat = Format(Now(), "dd mmm yyyy")

PayDate = Application.InputBox("Input Pay Date to appear on
Payslips",
"DATE REQUIRED", dateformat)

Range("A1").Value = PayDate

End Sub



I have recently been educated that using the "Option Explicit
Statement"
is a good way to ensure declaration of all variables and i tend to
agree
with this philosophy as the code is becoming larger (and complex!).
But
when i use the following code (including the declaration of
variables),
i
can't get it to provide the date format i require, ie "dd mmm yyyy".



Option Explicit

Sub DateTest()

Dim PayDate As Date, DateFormat As Date

DateFormat = Format(Now(), "dd mmm yyyy")

PayDate = Application.InputBox("Input Pay Date to appear on
Payslips",
"DATE REQUIRED", DateFormat)

Range("A1").Value = PayDate

End Sub



Using Option Explicit, how can i provide a default input of todays
date
in
"dd mmm yyyy" format and, following the user input insert the date
into
the spreadsheet in the format "dd mmm yyyy"?



Thanks in advance



Boeky