Anyone know why this date wont copy correctly? 
Author Message
 Anyone know why this date wont copy correctly?

I have a button which inserts a date in a cell using the following code:

Dim pdtDay as Date

pdtDay = #1/6/2003#
Sheets(1).Range("A1").Value = pdtDay

It then gets copied into another sheet

Sheets(2).Range("A1").Value = Sheets(1).Range("A1").Value

To make sure it is formatted how i want:

Sheets(2).Range("A1").NumberFormat = "dddd, mmmm d, yy"

All works fine.

When I replace the hard coded date with one entered in an input box however:

pdtDay = Application.InputBox("Enter a date in the format dd/mm/yy",
Type:=1)

The date entered doesn't reach the sheet it is copied to intact, it gets
reveresed (eg 1/10/03 becomes 10/1/03) and the formatting wont work.

I thought type 1 was a number and it looks fine on the source sheet so I
can't figure out why there si a problem. Can anyone help?

Regards, Mark Stephens



Fri, 25 Nov 2005 03:04:57 GMT  
 Anyone know why this date wont copy correctly?
When it comes to dates, VBA is "US-centric". It expects the date the user enters is in mm/dd/yy
format. If you want them to enter it otherwise, you need to parse the date yourself, i.e.

  Dim TextDate AS String
  Dim pdtDay As Date
  Dim v As Variant

  TextDate = Application.InputBox("Enter a date in the format dd/mm/yy", Type:=1)
  v = Split(TextDate, "/")  'this function requires XL2000 or XL-XP
  pdtDay = DateSerial(Val(v(2)), Val(v(1)), Val(v(0)))
  With Sheets(1).Range("A1")
    .Value2 = pdtDay
    .NumberFormat = "dd/mm/yy"
  End With

If you are using XL97 or earlier, you'll have to write the code to split the text into 2 pieces
at the slashes.

As an aside, you might want to consider using 4-digit years.

Quote:

>I have a button which inserts a date in a cell using the following code:

>Dim pdtDay as Date

>pdtDay = #1/6/2003#
>Sheets(1).Range("A1").Value = pdtDay

>It then gets copied into another sheet

>Sheets(2).Range("A1").Value = Sheets(1).Range("A1").Value

>To make sure it is formatted how i want:

>Sheets(2).Range("A1").NumberFormat = "dddd, mmmm d, yy"

>All works fine.

>When I replace the hard coded date with one entered in an input box however:

>pdtDay = Application.InputBox("Enter a date in the format dd/mm/yy",
>Type:=1)

>The date entered doesn't reach the sheet it is copied to intact, it gets
>reveresed (eg 1/10/03 becomes 10/1/03) and the formatting wont work.

>I thought type 1 was a number and it looks fine on the source sheet so I
>can't figure out why there si a problem. Can anyone help?

>Regards, Mark Stephens



Fri, 25 Nov 2005 04:23:56 GMT  
 Anyone know why this date wont copy correctly?
Thamks Myrna,

Thought there was something complicated going on! Will try the code and let
you know how I get on. Input much appreciated.

Kind regards, Mark


Quote:
> When it comes to dates, VBA is "US-centric". It expects the date the user

enters is in mm/dd/yy
Quote:
> format. If you want them to enter it otherwise, you need to parse the date
yourself, i.e.

>   Dim TextDate AS String
>   Dim pdtDay As Date
>   Dim v As Variant

>   TextDate = Application.InputBox("Enter a date in the format dd/mm/yy",
Type:=1)
>   v = Split(TextDate, "/")  'this function requires XL2000 or XL-XP
>   pdtDay = DateSerial(Val(v(2)), Val(v(1)), Val(v(0)))
>   With Sheets(1).Range("A1")
>     .Value2 = pdtDay
>     .NumberFormat = "dd/mm/yy"
>   End With

> If you are using XL97 or earlier, you'll have to write the code to split

the text into 2 pieces

- Show quoted text -

Quote:
> at the slashes.

> As an aside, you might want to consider using 4-digit years.



> >I have a button which inserts a date in a cell using the following code:

> >Dim pdtDay as Date

> >pdtDay = #1/6/2003#
> >Sheets(1).Range("A1").Value = pdtDay

> >It then gets copied into another sheet

> >Sheets(2).Range("A1").Value = Sheets(1).Range("A1").Value

> >To make sure it is formatted how i want:

> >Sheets(2).Range("A1").NumberFormat = "dddd, mmmm d, yy"

> >All works fine.

> >When I replace the hard coded date with one entered in an input box
however:

> >pdtDay = Application.InputBox("Enter a date in the format dd/mm/yy",
> >Type:=1)

> >The date entered doesn't reach the sheet it is copied to intact, it gets
> >reveresed (eg 1/10/03 becomes 10/1/03) and the formatting wont work.

> >I thought type 1 was a number and it looks fine on the source sheet so I
> >can't figure out why there si a problem. Can anyone help?

> >Regards, Mark Stephens



Fri, 25 Nov 2005 06:20:19 GMT  
 Anyone know why this date wont copy correctly?
Dear Myrna,

Just a {*filter*}, why is it better to use 4 digit years? Just curious,
regards, Mark

--
______________________________

Mark Stephens

Managing Director
Expat Gold - planning made easy:)
Tel: +44 161 8345256   Fax: +44 870 1399769
Singapore handphone: +65 9677 0684


Quote:
> When it comes to dates, VBA is "US-centric". It expects the date the user

enters is in mm/dd/yy
Quote:
> format. If you want them to enter it otherwise, you need to parse the date
yourself, i.e.

>   Dim TextDate AS String
>   Dim pdtDay As Date
>   Dim v As Variant

>   TextDate = Application.InputBox("Enter a date in the format dd/mm/yy",
Type:=1)
>   v = Split(TextDate, "/")  'this function requires XL2000 or XL-XP
>   pdtDay = DateSerial(Val(v(2)), Val(v(1)), Val(v(0)))
>   With Sheets(1).Range("A1")
>     .Value2 = pdtDay
>     .NumberFormat = "dd/mm/yy"
>   End With

> If you are using XL97 or earlier, you'll have to write the code to split

the text into 2 pieces

- Show quoted text -

Quote:
> at the slashes.

> As an aside, you might want to consider using 4-digit years.



> >I have a button which inserts a date in a cell using the following code:

> >Dim pdtDay as Date

> >pdtDay = #1/6/2003#
> >Sheets(1).Range("A1").Value = pdtDay

> >It then gets copied into another sheet

> >Sheets(2).Range("A1").Value = Sheets(1).Range("A1").Value

> >To make sure it is formatted how i want:

> >Sheets(2).Range("A1").NumberFormat = "dddd, mmmm d, yy"

> >All works fine.

> >When I replace the hard coded date with one entered in an input box
however:

> >pdtDay = Application.InputBox("Enter a date in the format dd/mm/yy",
> >Type:=1)

> >The date entered doesn't reach the sheet it is copied to intact, it gets
> >reveresed (eg 1/10/03 becomes 10/1/03) and the formatting wont work.

> >I thought type 1 was a number and it looks fine on the source sheet so I
> >can't figure out why there si a problem. Can anyone help?

> >Regards, Mark Stephens



Sat, 26 Nov 2005 00:58:41 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. WHY WONT ANYONE HELP ME :(

2. Anyone know why this code isn't working (all of a sudden)

3. This recursing sub crashes Excel - anyone know why?

4. Bizarre behaviour - anyone know why?

5. One field wont copy from one database to another, Can anyone help - contract_book.zip (0/1)

6. Does anyone know why....

7. My office programs freeze with mstordb.exe does anyone know why

8. Anyone know why I get this error

9. Does anyone know why CHECK Constraint text does not appear

10. Does anyone know where I can get a copy of Outlook 98


 
Powered by phpBB® Forum Software © phpBB Group