Date format issue

Hi all

I posted a variation on this request a couple of days back, but no-one seems
able (or willing) to solve my problem. Thsi issue is this:

I need to have the option of entering a date into a form either manually, or
from a command button. I then need that date to be transferred to a
worksheet cell. I then need to be able to compare other cells with that date
to determine if the other dates have passed.

I'm currently using a textbox on the form, but this shows the date as
mm/dd/yyyy instead of the required dd/mm/yyyy. The date on the sheet is
fine.
If I use Format(date, "dd/mm/yyyy)) to correctly display the date on the
form, the transferred date is then in text format and can no longer be
compared to on the sheet.

Any ideas how I can get round this?

--
Ian
--

Have a look at <windows>< control panel> regional and language options.

--
Greetings from New Zealand
Bill K
"Ian" <[email protected]> wrote in message
news:[email protected]...
> Hi all
>
> I posted a variation on this request a couple of days back, but no-one
> seems able (or willing) to solve my problem. Thsi issue is this:
>
> I need to have the option of entering a date into a form either manually,
> or from a command button. I then need that date to be transferred to a
> worksheet cell. I then need to be able to compare other cells with that
> date to determine if the other dates have passed.
>
> I'm currently using a textbox on the form, but this shows the date as
> mm/dd/yyyy instead of the required dd/mm/yyyy. The date on the sheet is
> fine.
> If I use Format(date, "dd/mm/yyyy)) to correctly display the date on the
> form, the transferred date is then in text format and can no longer be
> compared to on the sheet.
>
> Any ideas how I can get round this?
>
> --
> Ian
> --
>
>

whenever u use format function of vb it returns a string , in this case u need to set the format of the excel cell to which u r exporting the date value.
if u have in a textbox some date value convert it (if necessary) to date datatype using cdate() in vb and export to a excel cell which is formatted as date type - use format cells option.

-RR

Thanks for the suggestion, Bill, but this is not the issue as the date in
the cell appears correctly.

The VBA Date command seems to format as mm/dd/yyyy by default. Although I
can reformat using Format(Date, "dd/mm/yyyy"), the resulting data is then
text which is no use when transferred to my sheet.

--
Ian
--
"Bill Kuunders" <[email protected]> wrote in message
news:[email protected]...
> Have a look at <windows>< control panel> regional and language options.
>
> --
> Greetings from New Zealand
> Bill K
> "Ian" <[email protected]> wrote in message
> news:[email protected]...
>> Hi all
>>
>> I posted a variation on this request a couple of days back, but no-one
>> seems able (or willing) to solve my problem. Thsi issue is this:
>>
>> I need to have the option of entering a date into a form either manually,
>> or from a command button. I then need that date to be transferred to a
>> worksheet cell. I then need to be able to compare other cells with that
>> date to determine if the other dates have passed.
>>
>> I'm currently using a textbox on the form, but this shows the date as
>> mm/dd/yyyy instead of the required dd/mm/yyyy. The date on the sheet is
>> fine.
>> If I use Format(date, "dd/mm/yyyy)) to correctly display the date on the
>> form, the transferred date is then in text format and can no longer be
>> compared to on the sheet.
>>
>> Any ideas how I can get round this?
>>
>> --
>> Ian
>> --
>>
>>
>
>

Perfect!

I can leave TextBox1.Value = Format (Date, "dd/mm/yyyy") assigned to the
command button, then Range("E2") = Cdate(TextBox1.Value) is exactly what I
needed to convert the transferred data to date format.

Thank you very much.
--
Ian
--
"ramesh_rajamoni"
<[email protected]> wrote in
message news:[email protected]...
>
> whenever u use format function of vb it returns a string , in this case
> u need to set the format of the excel cell to which u r exporting the
> date value.
> if u have in a textbox some date value convert it (if necessary) to
> date datatype using cdate() in vb and export to a excel cell which is
> formatted as date type - use format cells option.
>
> -RR
>
>
> --
> ramesh_rajamoni
> ------------------------------------------------------------------------
> ramesh_rajamoni's Profile:
> http://www.excelforum.com/member.php...o&userid=29139
> View this thread: http://www.excelforum.com/showthread...hreadid=488492
>