Formatting date as first letter of day only

[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

 

[Per Jessen]

If you can do with two lettes, it is no problem.

Format > Custom > Type: ddd

Regards,
Per

 

[Pete_UK]

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

Pete

 

[GavinS]

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

Pete

No I cannot use 2 letters.
Distinguishing between Tues and Thursday - Tuesday comes after
Monday ;-)

 

[Dave Peterson]

Maybe you can use a helper cell with a formula:

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

(And hide the original column???)

 

[Don Guillett Excel MVP]

Maybe you can use a helper cell with a formula:

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

(And hide the original column???)

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

 

[Wouter HM]

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

 

[GavinS]

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.

 

[WallyWallWhackr]

Simple.


format as date and show only day of week. Then:

If('mycell'=Monday,"M")

And string seven IF statements together there to convert the full
printed day of week out as only a single letter.

For a workbook with a date field that comes close see these:

http://office.microsoft.com/en-us/templates/CT010117277.aspx#ai:TC030008309|

or

http://tinyurl.com/25kboja

 

[Gord Dibben]

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

 

[Guest]

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.

 

[GavinS]

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.

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

 

[CellShocked]

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

Try this:

It is using no special functions

http://www.mediafire.com/?ykh9nurz3zu9eeo

 

[GavinS]

  Try this:

  It is using no special functions

http://www.mediafire.com/?ykh9nurz3zu9eeo

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

 

[WallyWallWhackr]

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

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

 

[Wouter HM]

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

 

[CellShocked]

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

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

So it can never actually be only one letter per day.

 

[dranon]

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

So it can never actually be only one letter per day.

You don't listen too good.

 

[CellShocked]

You don't listen too good.

You're a goddamned idiot, boy. Go interlope somewhere else.

I was doing dates before you were even born.

 

[dranon]

You're a goddamned idiot, boy. Go interlope somewhere else.

I was doing dates before you were even born.

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.