PDA

View Full Version : [SOLVED] Problem Formatting Date for Display



Cyberdude
05-21-2005, 02:12 PM
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:


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:


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:


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??

Jacob Hilderbrand
05-21-2005, 02:19 PM
Your second code works fine for me.

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

Cyberdude
05-22-2005, 12:19 PM
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:

geekgirlau
05-22-2005, 04:26 PM
Now if there was only a way to stop you Yanks putting your dates in backwards ... :tongue2:

johnske
05-22-2005, 04:45 PM
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:

Cyberdude
05-23-2005, 11:26 AM
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!!!

johnske
05-23-2005, 03:52 PM
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:

Bob Phillips
05-23-2005, 05:11 PM
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: