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

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

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

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

Hi Bob,

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.

But it only works when the format is unambiguous, such as the "dd mmm yyyy"
in this example. If the OP had wanted dd/mm/yyyy, your code would have
resulted in the day and month being transposed. Hence, I consider the latter
"More controlled" - pass the number to Excel as a Double, then format the
cells to tell Excel how you want it represented.

There's much more about these sorts of issues at
http://www.oaltd.co.uk/ExcelProgRef/Ch22

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

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

I agree - more controlled to pass variable as numeric.
However, I write .Value from a VBA Date datatype. Is there an advantage to
convert to Double - or does it get converted to Double anyway (behind the
scenes)?

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


"Stephen Bullen" wrote in message
...
Hi Bob,

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.

But it only works when the format is unambiguous, such as the "dd mmm
yyyy"
in this example. If the OP had wanted dd/mm/yyyy, your code would have
resulted in the day and month being transposed. Hence, I consider the
latter
"More controlled" - pass the number to Excel as a Double, then format the
cells to tell Excel how you want it represented.

There's much more about these sorts of issues at
http://www.oaltd.co.uk/ExcelProgRef/Ch22

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Hi Rob,

I agree - more controlled to pass variable as numeric.
However, I write .Value from a VBA Date datatype. Is there an advantage to
convert to Double - or does it get converted to Double anyway (behind the
scenes)?

If you write a Date data type to a cell, Excel also gives the cell a default
'date' number format. Similarly, if you write a string that looks like a
date, Excel gives it a format something like that used in the string. Both
those waste a few cycles if you then want to format the cell properly. I
tested it with the following (in which my regional settings are DMY order):

Sub Test()

'Got 38300.45299
Range("A1").Value = CDbl(Now())

'Got 09/11/2004 10:52
Range("A2").Value = CDate(Now())

'Got 09-Nov-04 (notice, NOT the same as the date string entered!)
Range("A3").Value = "9 Nov 2004"

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Thanks for the info

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


"Stephen Bullen" wrote in message
...
Hi Rob,

I agree - more controlled to pass variable as numeric.
However, I write .Value from a VBA Date datatype. Is there an advantage
to
convert to Double - or does it get converted to Double anyway (behind the
scenes)?

If you write a Date data type to a cell, Excel also gives the cell a
default
'date' number format. Similarly, if you write a string that looks like a
date, Excel gives it a format something like that used in the string. Both
those waste a few cycles if you then want to format the cell properly. I
tested it with the following (in which my regional settings are DMY
order):

Sub Test()

'Got 38300.45299
Range("A1").Value = CDbl(Now())

'Got 09/11/2004 10:52
Range("A2").Value = CDate(Now())

'Got 09-Nov-04 (notice, NOT the same as the date string entered!)
Range("A3").Value = "9 Nov 2004"

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

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

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

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