Long Date Format from other sheet

[tourless]

Hi Folks,

I have a cell on a second sheet with date formatted as 'general', and I'd like to copy that value to another sheet and transpose it to a long date format. Essentially turning this...
2018-07-21
into this...
Saturday, July 21st, 2018
Here's my starting point.

wsRequired1.Range("B3").Value = "=DP-Required1!A2"

 

[JoeMo]

If the cell on the second sheet is A1 and holds a date (not text) then this will format that cell to a long date:

Sheets("name of 2nd sheet").range("A1").NumberFormat = "dddd, mmmm dd, yyyy"

 

[Rick Rothstein]

If the cell on the second sheet is A1 and holds a date (not text) then this will format that cell to a long date:

Sheets("name of 2nd sheet").range("A1").NumberFormat = "dddd, mmmm dd, yyyy"

You missed the ordinal suffix on the day number.

Hi Folks,

I have a cell on a second sheet with date formatted as 'general', and I'd like to copy that value to another sheet and transpose it to a long date format. Essentially turning this...
2018-07-21
into this...
Saturday, July 21st, 2018
Here's my starting point.

wsRequired1.Range("B3").Value = "=DP-Required1!A2"

Give this formula a try...

=SUBSTITUTE(TEXT(B3,"dddd, mmmm d\x, yyyy"),"x",MID("thstndrdth",MIN(9,2*RIGHT(DAY(B3))*(MOD(B3-11,100)>2)+1),2))

Note: The value in the cell you put this formula in will be text... there is no way to make it a real date value without using VBA code because of your requirement for the ordinal suffix on the day number.

 

[tourless]

Thanks guys. Here's what I ended up with...

wsRequired1.Range("B3") = Format(wsDPRequired1.Range("A2"), "dddd, mmmm dd, yyyy")

 

[Rick Rothstein]

Thanks guys. Here's what I ended up with...

wsRequired1.Range("B3") = Format(wsDPRequired1.Range("A2"), "dddd, mmmm dd, yyyy")

In your original post, you showed you wanted the ordinal suffix on the day number... the above line of code does not include an ordinal suffix... did you change your requirement without telling us?

 

[JoeMo]

You missed the ordinal suffix on the day number.

Yes, didn't see that, just the request for a long date. When a date is formatted as long from the ribbon it doesn't have an ordinal suffix.

 

[Rick Rothstein]

Yes, didn't see that, just the request for a long date. When a date is formatted as long from the ribbon it doesn't have an ordinal suffix.

Given what the OP wrote in Message #4 , I am wondering how fixed he was on the need for the ordinal suffix.

 

[JoeMo]

Given what the OP wrote in Message #4 , I am wondering how fixed he was on the need for the ordinal suffix.

Apparently, not at all fixed.

 

[Rick Rothstein]

Apparently, not at all fixed.

Heh-heh, yeah, I guess not.

On the off-chance that the OP would actually like the ordinal suffix attached to the day number, here are two different code lines for him to choose from that will do it for him...

[table="width: 500"]
[tr]
	[td]wsRequired1.Range("B3").Value = Evaluate(Replace("SUBSTITUTE(TEXT(@,""dddd, mmmm d\x, yyyy""),""x"",MID(""thstndrdth"",MIN(9,2*RIGHT(DAY(@))*(MOD(@-11,100)>2)+1),2))", "@", wsDPRequired1.Range("A2").Address)[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td]wsRequired1.Range("B3") = Replace(Format(wsDPRequired1.Range("A2"), "dddd, mmmm dd\x, yyyy"), "x", Mid$("thstndrdthththththth", 1 - 2 * ((Day(wsDPRequired1.Range("A2"))) Mod 10) * (Abs((Day(wsDPRequired1.Range("A2"))) Mod 100 - 12) > 1), 2))[/td]
[/tr]
[/table]

 

[tourless]

You are correct. In my original post I did indicate the ordinal suffix. My solution obviously did not include that, which was my compromise to move forward trying to complete my mission prior to eod. However, I will be revising my code to include not only on this but on many other reports that I've got generating throughout the week. Thanks for all the info guy... I learn a lot here and i appreciate all the input and feedback.