Day of Week and Date

T

Té

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

PhilosophersSage

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
 
R

Rick Rothstein

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)
 
R

Rick Rothstein

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)
 
M

Mike H

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
 
M

Mike H

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

Rick Rothstein said:
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)
Click to expand...
 
R

Rick Rothstein

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)
 
R

Rick Rothstein

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.
 
M

Mike H

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
 
R

Rick Rothstein

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.
 
T

Té

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

Rick Rothstein

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.
 
Top