Problem Formatting Date for Display

I just encountered a peculiar discrimination.
I wanted to extract a date from a cell, then include it in a Msgbox display. The cell has the date displayed as "mm/dd/yy". Note that there are just 2 year digits.
My VBA code looked like:

Code:

---

BillDate = Range("A1")
Msgbox "This is the date: " & BillDate

---

What is displayed is
This is the date: 05/22/2005 (A 4-digit year)
So I altered my code above to look like:

Code:

---

BillDate = Format(Range("A1"), "mm/dd/yy")
 Msgbox "This is the date: " & BillDate

---

and I got the same results with a 4-digit year. So I changed the code to be:

Code:

---

BillDate = Range("A1")
  Msgbox "This is the date: " & Format(BillDate, "mm/dd/yy")

---

and it worked!
Why is it that I can't format the date when putting it into a string variable, then display it, but it works when I format it in the Msgbox statement??

Your second code works fine for me.

Are you sure BillDate is a String Data Type and not a Date Data Type?

DRJ, you guessed it! My variable IS a type date. Just didn't think about that. Seems so obvious when you point it out. Thanx. :bow:

Now if there was only a way to stop you Yanks putting your dates in backwards ... :tongue2:

Quote:

---

Originally Posted by geekgirlau

Now if there was only a way to stop you Yanks putting your dates in backwards ... :tongue2:

---

:rotlaugh: I'll second that - it's VERY confusing to the rest of the world when there are only numbers involved, :bug: for numbered dates we always use the day-month-year (smaller periods of time to larger) convention and only use month-day-year when the month can be written in full - e.g. May 1st 2005 (which can then also be used as 1st May 2005) :devil:

I don't think of myself so much as a yank, but more of a tug.
As for date format, I personally ALWAYS write a full date as DD MMM YY. Well, ALMOST always. When I omit the year, then I write MMM DD ... sorry 'bout that. :thumb
Speaking of dates, I noticed that the date on this post is the 23rd, but the post before it is on the 24th. Migawd, I'm getting posts from the future!!!

Quote:

---

Originally Posted by Cyberdude

Speaking of dates, I noticed that the date on this post is the 23rd, but the post before it is on the 24th. Migawd, I'm getting posts from the future!!!

---

:rotlaugh: :rotlaugh: Hey, yup - it's future-land here (sumpin to do with the international date-line I think) :devil:

Quote:

---

Originally Posted by Cyberdude

I don't think of myself so much as a yank, but more of a tug.
As for date format, I personally ALWAYS write a full date as DD MMM YY. Well, ALMOST always. When I omit the year, then I write MMM DD ... sorry 'bout that.

---

I always push for the ISO standard yyyy-mm-dd, nobody can get confused then. Especially important in VBA where those darn Yanks force their date format on us :banghead: