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

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

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

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

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

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

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.



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

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

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

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

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

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

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

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

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!

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