Formatting date as first letter of day only


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
 
Ad

Advertisements

P

Per Jessen

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

Format > Custom > Type: ddd

Regards,
Per
 
P

Pete_UK

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

Pete
 
G

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 ;-)
 
D

Dave Peterson

Maybe you can use a helper cell with a formula:

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

(And hide the original column???)
 
D

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

Advertisements

W

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
 
G

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

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
 
G

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

Advertisements

G

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
 
W

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
 
W

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
 
Ad

Advertisements

C

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

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top