Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

custom format of:

dddd mmm d
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Té" wrote:

Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

you can do =TEXT(A1, "dddd, mmm dd")
Where A1 is your date, or you can custom format the cell for "dddd, mmm dd"
if you want to enter date in cell. Only thing is you will only get Oct-6
rather then Oct 6th

"Té" wrote:

Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

Hi,

=TEXT(E14,"dddd"),

or use this custom format

dddd,mmmd,yy



"Té" wrote:

Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

You can get Tuesday Oct. 6 by formatting as dddd mmm. d
but not 6th.
--
David Biddulph

"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

Assuming A1 contains a real date, this formula will give you the output you
want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

Whoops, I forgot the period after the abbreviated month name (also, I see
the newsreader broke the formula at an awkward location...fixed in this
formula)...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",
MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming A1 contains a real date, this formula will give you the output
you want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

Tested Rick?

What about the 21th of the month.

Maybe this, but I'm sure your now going to make it much shorter :)

=TEXT(A1,"dddd, mmm
d")&IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<= 14),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd"," rd","th","th","th","th","th","th"))

Mike

"Rick Rothstein" wrote:

Assuming A1 contains a real date, this formula will give you the output you
want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

I still think the wheels come off on 21th ;)

"Rick Rothstein" wrote:

Whoops, I forgot the period after the abbreviated month name (also, I see
the newsreader broke the formula at an awkward location...fixed in this
formula)...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",
MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming A1 contains a real date, this formula will give you the output
you want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

Thanks for spotting that... when I modified the ordinal part of the formula
from my original formula, I accidentally removed a RIGHT function call. Here
is the corrected formula...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",MIN(9,
2*RIGHT(DAY(A1))*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Tested Rick?

What about the 21th of the month.

Maybe this, but I'm sure your now going to make it much shorter :)

=TEXT(A1,"dddd, mmm
d")&IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<= 14),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd"," rd","th","th","th","th","th","th"))

Mike

"Rick Rothstein" wrote:

Assuming A1 contains a real date, this formula will give you the output
you
want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

And, as Mike pointed out, this formula needed a further modification...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",MIN(9,
2*RIGHT(DAY(A1))*(MOD(A1-11,100)2)+1),2)

The above formula should work correctly in all cases.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Whoops, I forgot the period after the abbreviated month name (also, I see
the newsreader broke the formula at an awkward location...fixed in this
formula)...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",
MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming A1 contains a real date, this formula will give you the output
you want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

For the life of me I couldn't see what you'd done wrong, I'd been playing
with it for ages before I gave up and wrote my own. I completely missed you
were only looking at the right part of the day. I'll scrap my long version
now and use yours in future :(

Mike

"Rick Rothstein" wrote:

Thanks for spotting that... when I modified the ordinal part of the formula
from my original formula, I accidentally removed a RIGHT function call. Here
is the corrected formula...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",MIN(9,
2*RIGHT(DAY(A1))*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Tested Rick?

What about the 21th of the month.

Maybe this, but I'm sure your now going to make it much shorter :)

=TEXT(A1,"dddd, mmm
d")&IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<= 14),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd"," rd","th","th","th","th","th","th"))

Mike

"Rick Rothstein" wrote:

Assuming A1 contains a real date, this formula will give you the output
you
want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

The problem was my original formula assumed a number in A1, not a date (the
formula is valid for all valid Excel whole numbers)... I mistakenly replaced
the RIGHT function call with a DAY function call to account for the date
value in A1 and just didn't think about what that meant. Again, thanks for
catching the problem in the first place.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
For the life of me I couldn't see what you'd done wrong, I'd been playing
with it for ages before I gave up and wrote my own. I completely missed
you
were only looking at the right part of the day. I'll scrap my long version
now and use yours in future :(

Mike

"Rick Rothstein" wrote:

Thanks for spotting that... when I modified the ordinal part of the
formula
from my original formula, I accidentally removed a RIGHT function call.
Here
is the corrected formula...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",MIN(9,
2*RIGHT(DAY(A1))*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Tested Rick?

What about the 21th of the month.

Maybe this, but I'm sure your now going to make it much shorter :)

=TEXT(A1,"dddd, mmm
d")&IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<= 14),"th",CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd"," rd","th","th","th","th","th","th"))

Mike

"Rick Rothstein" wrote:

Assuming A1 contains a real date, this formula will give you the
output
you
want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for
example
Tuesday Oct. 6th.

Is there a shortcut where it puts in the day and date? like ctrl ; does the
date, but is there an easy way where I can get the day of the week and the
month in one cell?

"Rick Rothstein" wrote:

Whoops, I forgot the period after the abbreviated month name (also, I see
the newsreader broke the formula at an awkward location...fixed in this
formula)...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",
MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming A1 contains a real date, this formula will give you the output
you want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for example
Tuesday Oct. 6th.

Why not try what has been suggested?
--
David Biddulph

"Té" wrote in message
...
Is there a shortcut where it puts in the day and date? like ctrl ; does
the
date, but is there an easy way where I can get the day of the week and the
month in one cell?

"Rick Rothstein" wrote:

Whoops, I forgot the period after the abbreviated month name (also, I see
the newsreader broke the formula at an awkward location...fixed in this
formula)...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",
MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming A1 contains a real date, this formula will give you the output
you want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for
example
Tuesday Oct. 6th.

I can give you a VB solution that will format the cell with the display
value you want and still keep the cell value as the date you enter. However,
you will have to identify, in advance, the cell or range of cells you want
it to apply to. For example, assuming you want this display functionality to
apply to the range A1:C9 (you can change this address range inside the code
as needed), then do the following...

Right click the tab at the bottom of the worksheet where the cells A1:C9 are
located, select View Code from the popup menu that appears and Copy/Paste
the following into the code windows that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C9")) Is Nothing _
And IsDate(Target.Value) Then
Target.NumberFormat = "dddd mmm. d""" & _
Mid$("thstndrdthththththth", 1 - _
2 * ((Day(Target.Value)) Mod 10) * _
(Abs((Day(Target.Value)) Mod 100 - 12) 1), _
2) & """"
Else
Target.NumberFormat = "General"
End If
End Sub

Now, go back to the worksheet and type in a date in one of the cells in the
specified range... it should display as you wanted.

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a shortcut where it puts in the day and date? like ctrl ; does
the
date, but is there an easy way where I can get the day of the week and the
month in one cell?

"Rick Rothstein" wrote:

Whoops, I forgot the period after the abbreviated month name (also, I see
the newsreader broke the formula at an awkward location...fixed in this
formula)...

=TEXT(A1, "dddd, mmm. d")&MID("thstndrdth",
MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Assuming A1 contains a real date, this formula will give you the output
you want...

=TEXT(A1, "dddd, mmm
d")&MID("thstndrdth",MIN(9,2*DAY(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Té" wrote in message
...
Is there a way I can have the day of the week and the date, for
example
Tuesday Oct. 6th.