Text formatted for Days and Dates

[Srinivasulu B]

I am preparing an electronic diary.
For this I require two pieces of texts (for all the days in 2010) in the
following format.
01-01-2010 (Friday)
01-01-2010 (Fri)
I did the same for 2009...but for I forgot the steps and I do not have the
spread sheet with me now.
For this I have entered 01-01-10 in B1after formatting it as dd-mm-yy
In B2 I have enetered B1+1 and copied upto B365
Cells B2to B365 were formatted as dd-mm-YY.
Then in cell A1 I entered =B1 and formatted as DDDD. I got all the days in
the format "Friday"
Then I typed =A1+1, in A2 and copied upto A365.
I got all the days in the required format (days fully spelt out)
Then in C1 I entered =B1 and formatted for DDD. I got "Fri", which is fine.
Until this things worked as expected.
Now in Columns D and E, I want text exactly in the following formats.
1st. Jan 2010 (Friday), 01-01-2010 (Fri) respectively...so that I can
copy these two columns as text in the destination application.
This is where I got stuck.
But I did this for 2009 last year..for sure....but forgot the steps.
Can any one help as to how to proceed further to get the desired result.?
seena

 

[Ron Rosenfeld]

I am preparing an electronic diary.
For this I require two pieces of texts (for all the days in 2010) in the
following format.
01-01-2010 (Friday)
01-01-2010 (Fri)

This part is easy.

With the date in some cell:

Format/Cells/Number/Custom
Type: mm/dd/yyyy (ddd)
or
Type: mm/dd/yyyy (dddd)

I did the same for 2009...but for I forgot the steps and I do not have the
spread sheet with me now.
For this I have entered 01-01-10 in B1after formatting it as dd-mm-yy
In B2 I have enetered B1+1 and copied upto B365
Cells B2to B365 were formatted as dd-mm-YY.
Then in cell A1 I entered =B1 and formatted as DDDD. I got all the days in
the format "Friday"
Then I typed =A1+1, in A2 and copied upto A365.
I got all the days in the required format (days fully spelt out)
Then in C1 I entered =B1 and formatted for DDD. I got "Fri", which is fine.
Until this things worked as expected.
Now in Columns D and E, I want text exactly in the following formats.
1st. Jan 2010 (Friday), 01-01-2010 (Fri) respectively...so that I can
copy these two columns as text in the destination application.
This is where I got stuck.

For 01-01-2010 (Fri) see above

For 1st. Jan 2010 (Friday) you will need to use VBA. Do you want the entry to
be a "real" Excel date? or a text string?
--ron

 

[Srinivasulu B]

For 1st. Jan 2010 (Friday) you will need to use VBA. Do you want the
entry to be a "real" Excel date? or a text string?
--ron

I want a text string
This text string will be copied and pasted in a page maker application
seena

---------------------------------------

 

[Gary''s Student]

If B1 contains 01-01-10, then in D1 enter:

=DAY(B1)&IF(AND(MOD(DAY(B1),100)>=10,MOD(DAY(B1),100)<=14),"th",CHOOSE(MOD(DAY(B1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
& ". " & TEXT(B1,"mmm yyyy") & " (" & TEXT(B1,"dddd") & ")"

and copy down.

 

[Srinivasulu B]

Gary,
You ae simply great...simply great.
It works(ed) beatifully.
seena

 

[Rick Rothstein]

Here is a shorter formula that does the same thing as your formula...

=DAY(B1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(B1))*
(MOD(B1-11,100)>2)+1),2)&". "&TEXT(B1,"mmm yyyy (dddd)")

 

[Rick Rothstein]

By the way, did you see my last posting in your precedents tracing (Subject:
Position of a Shape) thread? I posted some code there after your last
message to me, but you never responded after that, so I don't know if you
saw it or not.

 

[Srinivasulu B]

I don't recollect any such thing.
If you have access to it...can you post it again.
I can tell you on seeing it.
It is very unlikely that I have not responded to a reply
Any way can you post it again
seena

 

[Rick Rothstein]

Sorry, wrong formula, this is what I meant to post...

=DAY(B15)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(B15))*
(MOD(DAY(B15)-11,100)>2)+1),2)&". "&TEXT(B15,"mmm yyyy (dddd)")

 

[Rick Rothstein]

My question was directed to Gary''s Student, not you (look at whose message
it was posted against). But, as long as you have come back to this thread (I
didn't think you would), you can consider using this shorter formula...

=DAY(B15)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(B15))*
(MOD(DAY(B15)-11,100)>2)+1),2)&". "&TEXT(B15,"mmm yyyy (dddd)")

which uses four less function calls and, hence, should be slightly more
efficient. The formula I originally posted to Gary''s Student had a minor
omission in it... I just posted the correction (the above formula) against
my first message to him.

 

[Rick Rothstein]

I'm sure you noticed that I mistakenly use B15 instead of B1 in my posted
formula; here it is with the B1 reference instead...

=DAY(B1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(B1))*
(MOD(DAY(B1)-11,100)>2)+1),2)&". "&TEXT(B1,"mmm yyyy (dddd)")

 

[Gary''s Student]

First let me apologize for not giving you feedback sooner. Your solution
worked just fine.

You have directly helped me a number of times in the past, and I appreciate
it.


I REALLY appreciate your willingnes to share techniques as well as
solutions. I learn something new every time you suggest an improvement to
something I have posted.

 

[Rick Rothstein]

See inline comments...

First let me apologize for not giving you feedback sooner. Your
solution worked just fine.

No need to apologize... you are under no obligation to post follow up
messages to me; it is just that in your acknowledgement of my Boolean
function you added additional details as to what you were ultimately trying
to do and I wanted to make sure you had a chance to evaluate the solution I
"cooked" up for you to do it... I'm glad you found it useful.

You have directly helped me a number of times in the past, and
I appreciate it.
.....
I REALLY appreciate your willingnes to share techniques as well
as solutions.

It has been, and will always be, my pleasure to do so.

I learn something new every time you suggest an
improvement to something I have posted.

I consider that to be high praise indeed, especially given that you are a
volunteer in these newsgroups as well... thank you very much for this great
compliment... I **really** appreciate it. I guess I can be expecting you to
change your online name to Rick''s Student in the near future, then?<vbg>