Formatting date as first letter of day only
 

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

Formatting date as first letter of day only
 

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

Format Custom Type: ddd

Regards,
Per

On 16 Dec., 07:42, GavinS wrote:
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

Formatting date as first letter of day only
 

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

Pete

On Dec 16, 6:42*am, GavinS wrote:
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

Formatting date as first letter of day only
 

On Dec 16, 8:28*pm, Pete_UK wrote:
If you did this, how would you distinguish between Tuesday/Thursday,
or between Saturday/Sunday?

Pete

On Dec 16, 6:42*am, GavinS wrote:

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

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

Formatting date as first letter of day only
 

Maybe you can use a helper cell with a formula:

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

(And hide the original column???)

On 12/16/2010 00:42, GavinS wrote:
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

--
Dave Peterson

Formatting date as first letter of day only
 

On Dec 16, 6:20*am, Dave Peterson wrote:
Maybe you can use a helper cell with a formula:

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

(And hide the original column???)

On 12/16/2010 00:42, GavinS wrote:

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

--
Dave Peterson

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

Formatting date as first letter of day only
 

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

Formatting date as first letter of day only
 

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.



On Dec 17, 5:20*am, Wouter HM wrote:
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

Formatting date as first letter of day only
 

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

Formatting date as first letter of day only
 

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



On Thu, 16 Dec 2010 15:25:16 -0800 (PST), GavinS
wrote:

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.



On Dec 17, 5:20*am, Wouter HM wrote:
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

Formatting date as first letter of day only
 

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.

On Wed, 15 Dec 2010 22:42:22 -0800 (PST), GavinS
wrote:

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

Formatting date as first letter of day only
 

On Dec 18, 3:24*am, wrote:
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.

On Wed, 15 Dec 2010 22:42:22 -0800 (PST), GavinS
wrote:

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

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

Formatting date as first letter of day only
 

On Fri, 17 Dec 2010 17:31:05 -0800 (PST), GavinS
wrote:

On Dec 18, 3:24*am, wrote:
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.

On Wed, 15 Dec 2010 22:42:22 -0800 (PST), GavinS
wrote:

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

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

Formatting date as first letter of day only
 

On Dec 19, 5:13*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet. org wrote:
On Fri, 17 Dec 2010 17:31:05 -0800 (PST), GavinS



wrote:
On Dec 18, 3:24 am, wrote:
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.

On Wed, 15 Dec 2010 22:42:22 -0800 (PST), GavinS
wrote:

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

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

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

Formatting date as first letter of day only
 

On Sat, 18 Dec 2010 18:26:11 -0800 (PST), GavinS
wrote:

On Dec 19, 5:13*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet .org wrote:
On Fri, 17 Dec 2010 17:31:05 -0800 (PST), GavinS



wrote:
On Dec 18, 3:24 am, wrote:
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.

On Wed, 15 Dec 2010 22:42:22 -0800 (PST), GavinS
wrote:

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

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

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/ma...0000658&ats=tc

Formatting date as first letter of day only
 

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

Formatting date as first letter of day only
 

On Sun, 19 Dec 2010 02:40:09 -0800 (PST), Wouter HM
wrote:

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.

Formatting date as first letter of day only
 

On Sun, 19 Dec 2010 06:42:14 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

On Sun, 19 Dec 2010 02:40:09 -0800 (PST), Wouter HM
wrote:

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.

You don't listen too good.

Formatting date as first letter of day only
 

On Mon, 20 Dec 2010 17:04:37 -0800, dranon
wrote:

On Sun, 19 Dec 2010 06:42:14 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet .org wrote:

On Sun, 19 Dec 2010 02:40:09 -0800 (PST), Wouter HM
wrote:

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.

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.

Formatting date as first letter of day only
 

On Mon, 20 Dec 2010 18:34:42 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

On Mon, 20 Dec 2010 17:04:37 -0800, dranon
wrote:

On Sun, 19 Dec 2010 06:42:14 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadshee t.org wrote:

On Sun, 19 Dec 2010 02:40:09 -0800 (PST), Wouter HM
wrote:

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.

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.

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.

Formatting date as first letter of day only
 

On Mon, 20 Dec 2010 22:22:45 -0800, dranon
wrote:

On Mon, 20 Dec 2010 18:34:42 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet .org wrote:

On Mon, 20 Dec 2010 17:04:37 -0800, dranon
wrote:

On Sun, 19 Dec 2010 06:42:14 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadshe et.org wrote:

On Sun, 19 Dec 2010 02:40:09 -0800 (PST), Wouter HM
wrote:

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.

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.

LOL.


The gang boy retard lingo here says a lot about you, little boy.

And what does that have to do with the fact that you don't
listen too good?


I listen fine. Where is it that you seem to think I did not listen
good? It has more to do with the fact that you jack off at the mouth
good.

Get your head out of your ass and read what has been
written.

I wrote some of it, idiot. Unlike you, another mouthy little bitch,
contributing nothing, but jacking off at the mouth,like the mental
masturbator that you are.

And when you make a suggestion that is contrary to logic
after taking that into consideration, apologize for being the idiot
you are.

You are the idiot that did not read the entire thread. I already gave
solutions to the guy, and merely commented on the non-solution that was
given here.

If your head was not so far up your ass, you would know that it was
your character which YOU compromised by taking your head out of your ass
long enough to toss your lame, peanut gallery wanna be comment into the
thread, instead of actually contributing to the thread.

It might have had an inkling of validity if you had actually
contributed to the thread. As it stands, you do not even rate the monkey
slapping his little cymbals together, boy.

Formatting date as first letter of day only
 

=CHOOSE(WEEKDAY(B2;1);"Su";"M";"Tu";"W";"Th";"F";" Sa")

Gets the prize

Although
=MID("SMTWTFS",WEEKDAY(A1),1)
was good it could not handle the 2nd letter thing ie Sa

Thanks for every ones help

Formatting date as first letter of day only
 

On Tue, 21 Dec 2010 04:38:50 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

LOL.


The gang boy retard lingo here says a lot about you, little boy.

Gang boy? My lord are you off base.


And what does that have to do with the fact that you don't
listen too good?


I listen fine. Where is it that you seem to think I did not listen
good?

You really expect me to do your work for you, brainless one? You
should live so long.

Your diatribe says more about you than me.

Some day you may grow up and learn a bit along the way.

Until then, I will comment when I feel like it and your silly
responses certainly won't have any impact on that.

You may continue if you wish. Some day I may even give you the last
word. Until then, be aware that if you post, I will reiterate your
brainless efforts.

I wrote some of it, idiot.

And no doubt just read the part that you wrote.

Formatting date as first letter of day only
 

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Gang boy? My lord are you off base.


So, "LOL" is "mature adult"? I don't think so.

Formatting date as first letter of day only
 

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Your diatribe says more about you than me.

The immature horse**** I snipped that was your diatribe says mountains
about you.

Contribute or **** off. Oh... it's you... just **** off then, as you
are obviously incapable of contributing.

Formatting date as first letter of day only
 

=CHOOSE(WEEKDAY(B2;1);"Su";"M";"Tu";"W";"Th";"F";" Sa")

Gets the prize

Although
=MID("SMTWTFS",WEEKDAY(A1),1)
was good it could not handle the 2nd letter thing ie Sa

If you wanted to use MID with 1 and 2 letter abbreviations, you could do it
this way (note the strategically located spaces)...

=TRIM(MID("SuM TuW ThF Sa",2*WEEKDAY(A1)-1,2))

Rick Rothstein (MVP - Excel)

Formatting date as first letter of day only
 

On Tue, 21 Dec 2010 19:09:39 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Gang boy? My lord are you off base.


So, "LOL" is "mature adult"? I don't think so.

No, it means exactly what it says, that I was laughing out loud at
your stupity.

Formatting date as first letter of day only
 

On Tue, 21 Dec 2010 19:12:33 -0800, StickThatInYourPipeAndSmokeIt
wrote:

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Your diatribe says more about you than me.

The immature horse**** I snipped that was your diatribe says mountains
about you.

Contribute or **** off. Oh... it's you... just **** off then, as you
are obviously incapable of contributing.

Name change to protect the stupid?

Formatting date as first letter of day only
 

On Wed, 22 Dec 2010 15:37:16 -0800, dranon
wrote:

On Tue, 21 Dec 2010 19:09:39 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet .org wrote:

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Gang boy? My lord are you off base.


So, "LOL" is "mature adult"? I don't think so.

No, it means exactly what it says, that I was laughing out loud at
your stupity.

No, I was referring to the fact that using it in writing is immature.

Come back when your mental age exceeds that of someone just entering
puberty. Better yet, just squirm some more. I am sure many are amused.

You are proof that numerical age does not an adult make.

And no, pkb boy, I am referring to you, idiot.

Formatting date as first letter of day only
 

On Wed, 22 Dec 2010 15:38:01 -0800, dranon
wrote:

On Tue, 21 Dec 2010 19:12:33 -0800, StickThatInYourPipeAndSmokeIt
wrote:

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Your diatribe says more about you than me.

The immature horse**** I snipped that was your diatribe says mountains
about you.

Contribute or **** off. Oh... it's you... just **** off then, as you
are obviously incapable of contributing.

Name change to protect the stupid?

Having further problems with reading comprehension again, I see.

Here, idiot... try another.. maybe it will begin to sink in.

Formatting date as first letter of day only
 

On Thu, 23 Dec 2010 05:51:38 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

On Wed, 22 Dec 2010 15:37:16 -0800, dranon
wrote:

On Tue, 21 Dec 2010 19:09:39 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadshee t.org wrote:

On Tue, 21 Dec 2010 16:17:40 -0800, dranon
wrote:

Gang boy? My lord are you off base.


So, "LOL" is "mature adult"? I don't think so.

No, it means exactly what it says, that I was laughing out loud at
your stupity.

No, I was referring to the fact that using it in writing is immature.

Come back when your mental age exceeds that of someone just entering
puberty. Better yet, just squirm some more. I am sure many are amused.

You are proof that numerical age does not an adult make.

And no, pkb boy, I am referring to you, idiot.

LOL. At you. And you refer to me as immature? You haven't got a clue
and never will. When an acronym is nothing more than a literal
abbreviation it takes a truly insecure individual to render it gang
speak. How about GFY?

Formatting date as first letter of day only
 

On Thu, 23 Dec 2010 13:28:06 -0800, dranon
wrote:

When an acronym is nothing more than a literal
abbreviation it takes a truly insecure individual to render it gang
speak.


Now, you do not even know what the meaning of the word "acronym" is.

And it was not I that "rendered it gangspeak". Pants down past the
asscrack retards like you did a fine job of that all by yourselves.

Formatting date as first letter of day only
 

On Thu, 23 Dec 2010 17:57:28 -0800, My Name Is Tzu How Do You Do
wrote:

On Thu, 23 Dec 2010 13:28:06 -0800, dranon
wrote:

When an acronym is nothing more than a literal
abbreviation it takes a truly insecure individual to render it gang
speak.


Now, you do not even know what the meaning of the word "acronym" is.

And it was not I that "rendered it gangspeak". Pants down past the
asscrack retards like you did a fine job of that all by yourselves.

Now your stupidity is overflowing. Besides your attempt to rewrite
history you now want to change the common definition of words.

Truly pathetic.

You really do need to learn to read.

Formatting date as first letter of day only
 

On Thursday, December 16, 2010 11:42:22 AM UTC+5, GavinS wrote:
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

USE THIS CODE:

string stTodayDayFirstLetter = DateTime.Now.ToString("ddd").Substring(0, 1);

Formatting date as first letter of day only
 

On Friday, December 17, 2010 at 11:24:17 AM UTC-5, wrote:
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.

On Wed, 15 Dec 2010 22:42:22 -0800 (PST), GavinS
wrote:

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

AWESOME!!! Worked perfectly...been searching multiple posts and this one finally worked and so easily. One small change I made was substituting N for Sunday and R for Thursday in the "SMTWTFS" text string...now =MID("NMTWRFS",WEEKDAY(A1),1)...again, thanks a ton!