Format Date to get August 24, 2012

[rcreek09]

I have tried every format I can think of to get "August 24, 2012" and I cannot.

What am I doing wrong????

Code:

Sub formatthedate()


Dim thisweek As Date

Dim lngDateFormatType As Long

lngDateFormatType = Application.International(xlDateOrder)

'Order of date elements:

'0 = month-day-year

'1 = day-month-year

'2 = year-month-day

'Just checking to be sure -- mine returns a 0!!!!!


Range("a3").Value = lngDateFormatType


thisweek = Right(Range("a14"), 10)


Range("d4").Value = Format(thisweek, "mmmm dd, yyyy")

Range("d5").Value = Format(thisweek, "mmm dd, yyyy")

Range("d6").Value = Format(thisweek, "Long date")

Range("d7").Value = Format(thisweek, "general date")

Range("d8").Value = Format(thisweek, "medium date")


End Sub


Output:


0 (in A3)

(Starting in D4)

24-Aug-12

24-Aug-12

Friday, August 24, 2012

8/24/2012

24-Aug-12

A14:

Week Ending 2012-08-24


Have tried `format(thisweek, "mmmmmmmmm dd, yyyy") and get AugustAug8 24, 2012!


Any help is greatly appreciated.


Thanks very much,

Deb

 

[SirJB7]

Hi, rcreek09!


The string "mmmm dd, yyyy" works for me both as custom format for a cell and for parameter for Format function in VBA.


Give a look at this file:

https://dl.dropbox.com/u/60558749/Format%20Date%20to%20get%20_August%2024%2C%202012_%20%28for%20rcreek09%20at%20chandoo.org%29.xlsm


I did:

A1: 24/08/2012

B1: =A1

Formatted B1 with the reluctant string

C1: =A1

Run procedure SlipperyFormat on code of worksheet "Hoja1".


Hope it helps.


Regards!

 

[rcreek09]

Thank you Sir, for the speedy reply.

Unfortunately, I cannot access dropbox from work but I will try this as soon as I get home this evening. Without seeing the file/code, I'm not following you very well, but I trust it will be clear when I can get the file.


The "mmmm dd, yyyy" works for me as a cell function, =text(d4,"mmmm dd,yyyy") in Excel, but I cannot get it work as I expect in vba.


The only way I can get the month spelled out is with Long Date, and that gives me the DAY which I do not want!


Anyway, am anxious to see the SlipperyFormat.


Thank you very much for your help.

Deb

 

[SirJB7]

Hi, rcreek09!

Here's the code, place it in any module or workbook/worksheets code sections:

-----

[pre]

Option Explicit

Sub SlipperyFormat()
Range("C1").Value = Format(Range("A1").Value, "mmmm dd, yyyy")
End Sub

[/pre]
-----

Just advise if any issue.

Regards!

 

[Debraj]

Hi rcreek09,

Just a Normal Modification in SirJB7's Code..

[pre]

Option Explicit

Sub SlipperyFormat()
Range("C1").Value = Range("A1").Value
Range("C1").NumberFormat = "mmmm dd,yyyy"
End Sub

[/pre]
Regards,

Deb

 

[rcreek09]

Here is what I ended up using. I could NOT get the vba format to work as I expected.

An awful lot of trouble just to get this:

August 25, 2012 - August 31, 2012 vs August 27, 2011 - September 02. 2011


Code:

thisweek = Right(Range("a13"), 10)

thisweekty = thisweek

thisweekly = thisweekty - 364

thisweekbegly = thisweekly - 6

thisweekbegty = thisweek - 6


twendty = Application.WorksheetFunction.Text(thisweek, "mmmm dd, yyyy")

twendly = Application.WorksheetFunction.Text(thisweekly, "mmmm dd, yyyy")

twbegly = Application.WorksheetFunction.Text(thisweekbegly, "mmmm dd, yyyy")

twbegty = Application.WorksheetFunction.Text(thisweekbegty, "mmmm dd, yyyy")


Sheets(Array(1, 2, 3, 4, 5, 6)).Select

Range("f2").Select

ActiveCell.Formula = _

"=INDEX(divs,MATCH(TRIM(MID(a13,6,2)),divs3,0),2)"

Range("A2").Select

ActiveCell.Formula = twbegty & " - " & twendty & " vs " & twbegly & " - " & twendly


I certainly do appreciate your help VERY much!

Thank you,

Deb

 

[SirJB7]

Hi, rcreek09!

Would you post the sample files with Debraj Roy's and mine's solutions just to check the arising issue?

Regards!