When in doubt on dates, ask Norman (Harker that is).

Bob

"Rob van Gelder" wrote in message
...
You were shaking my already shaky understanding of Excel's date handling!

Glad thats sorted. Cheers

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


"Bob Phillips" wrote in message
...
Sorry, I see where you are now.

I was thinking, but not saying

With Range("A1")
.Value = PayDate
.NumbedrFormat = "dd mmm yyyy"
End With

--

HTH

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


"Rob van Gelder" wrote in
message
...
I still don't get your logic.

You wrote:
You still need to format the result

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

Do you really expect that code to change the cell formatting - or is it
a
typo?


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


"Bob Phillips" wrote in message
...
No, I am not saying it wasn't a date, I am saying that as given the
input
box date format was dd mmm yyyy, but the cell format was not
necessarily
that (dd-mmm-yy in my case), so I coerced the format.

Thus to get the cell formatted as the OP wanted (which I accept was
not
explicitly stated one way or the other, but a reasonable assumption),
then
you either force the format in the code, or format the cells. I
prefer
the
former as it is more controlled.

Your original code did not do the latter, your latest post did.


--

HTH

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


"Rob van Gelder" wrote in
message
...
I hate to extend a thread further than it has to, but I'm confused.

PayDate is a Date variable
and you're saying that Range("A1").Value = PayDate does not result
in
A1
being a date?

I see no coersion here.

Sub test()
Dim PayDate As Date

PayDate = Now

Range("A1").Value = PayDate

Range("A1").NumberFormat = "dd mmm yyyy"
End Sub



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


"Bob Phillips" wrote in message
...
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