Formatting date as first letter of day only

  • Thread starter Thread starter GavinS
  • Start date Start date
G

GavinS

In various cells I have dates written in as dd/mm/yy format.

I would like to format this as d - that is the first of the day only.
For example 16/12/10 displayed as T (for Thursday).

How can I do this please
 
If you can do with two lettes, it is no problem.

Format > Custom > Type: ddd

Regards,
Per
 
If you did this, how would you distinguish between Tuesday/Thursday,
or between Saturday/Sunday?

Pete
 
If you did this, how would you distinguish between Tuesday/Thursday,
or between Saturday/Sunday?

Pete
Click to expand...

No I cannot use 2 letters.
Distinguishing between Tues and Thursday - Tuesday comes after
Monday ;-)
 
Maybe you can use a helper cell with a formula:

=left(text(a1,"ddd"),1)

(And hide the original column???)
 
Maybe you can use a helper cell with a formula:

=left(text(a1,"ddd"),1)

(And hide the original column???)
Click to expand...

If you no longer need the date for calculation you could just type in
T or W or you could have a macro do it all for you. Now, if you DO
need for future calculation a macro or formula looking for the T after
M for Tuesday. However, you would no longer be able to use the actual
date....
 
Hi Gavin,

You can create your own User Defined Function.
Eg:

Public Function FirstOfWeekday(aDate As Date) As String

Dim iWeekday As Integer

iWeekday = Weekday(aDate, vbSunday)

FirstOfWeekday = Choose(iWeekday, "S", "M", "T", "W", "T", "F",
"S")

End Function

On your sheet you enter the formula =FirstOfWeekday(Now()).


HTH,

Wouter
 
Thanks for all your responses. Regretfully the solution is way too
complicated. TO explain.

I have a time sheet - user enters in the first day of the month.
Another cell points to this first day and interprets this date as a
day - which I can just format as ddd - but it takes up too much space.
To teh right of this cell is the remaining days of the month -
formatted as ddd.

So Wouter's suggestion is the best but not dynamic enough - because
the 1st of every month never falls on the same day.
 
Wouter's UDF will be copied to a general module in your workbook.

First of month date in A1 or.........your choice.

In "Another cell" enter =FirstOfWeekDay(A1)


Gord Dibben MS Excel MVP
 
You could use:
=MID("SMTWTFS",WEEKDAY(A1),1) 'A1 is the cell with the date in it
Or if you want a unique letter for each day of the week you could use the
2nd letter Thursday & Saturday
=MID("SMTWHFA",WEEKDAY(A1),1)
or whatever scheme you wish.
 
You could use:
        =MID("SMTWTFS",WEEKDAY(A1),1)   'A1 is the cell with the date in it
Or if you want a unique letter for each day of the week you could use the
2nd letter Thursday & Saturday
        =MID("SMTWHFA",WEEKDAY(A1),1)
or whatever scheme you wish.
Click to expand...

Thanks, I think this is the simplest although is the WEEKDAY function
only included in an addin? I will find out.
Thanks for the help
 
Thanks - another great and reasonably simple solution.
My challenge is that I live in a 3rd world country and all solutions
must be simple and be able to possibly understood by others whose
knowledge of excel (and english for that matter ) is limited
Click to expand...

Which is why most if not all of my sheets are macro free, whenever
possible.

Also, Microsoft user submitted templates must be, as a rule.

I have a blood pressure workbook that has the macro text included for
user installation merely for hiding leap year data in the chart sheets
without jumping though hoops between charts and data. It is among my
other sheets on that site. My macros do not extend much farther than
that. It was recorded.


OOOps... my MS posted stuff, that is...

http://office.microsoft.com/en-us/managementpreview.aspx?AssetId=PN030000658&ats=tc
 
Hi Gavin,

I created an other option:


=CHOOSE(WEEKDAY(B2;1);"S";"M";"T";"W";"T";"F";"S")

where B2 holds the date.

So something without VBA


HTH,

Wouter
 
Hi Gavin,

I created an other option:


=CHOOSE(WEEKDAY(B2;1);"S";"M";"T";"W";"T";"F";"S")

where B2 holds the date.

So something without VBA


HTH,

Wouter
Click to expand...

Thursday and Saturday and or Sunday need additional letters to
differentiate.

So it can never actually be only one letter per day.
 
You're a goddamned idiot, boy. Go interlope somewhere else.

I was doing dates before you were even born.
Click to expand...

LOL. And what does that have to do with the fact that you don't
listen too good? Get your head out of your ass and read what has been
written. And when you make a suggestion that is contrary to logic
after taking that into consideration, apologize for being the idiot
you are.
 
Back
Top