Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ;-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
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); |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATE() Month in Letter NOT Number | Excel Worksheet Functions | |||
date in Excel displays incorrectly when merged in letter | Excel Discussion (Misc queries) | |||
Number zero and the Letter O formatting | Excel Programming | |||
press letter and go 2 entry begin w letter in data validation drop | Excel Programming | |||
Find out from a date if it is a red letter day or not | Excel Programming |