Excel Date??
 

I was wanting to format a date that would look like this Mon-20th. If
any one could help i would greatly appreciate it.

Excel Date??
 

=TEXT(A1,"ddd
dd")&CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd", "th","th","th","th","th","
th")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"slvtenn" wrote in message
oups.com...
I was wanting to format a date that would look like this Mon-20th. If
any one could help i would greatly appreciate it.

Excel Date??
 

Correction, it mis-handles some days

=TEXT(A1,"ddd dd")&IF(AND(DAY(A1)=10,DAY(A1)<=14),"th",
CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"slvtenn" wrote in message
oups.com...
I was wanting to format a date that would look like this Mon-20th. If
any one could help i would greatly appreciate it.

Excel Date??
 

ok that was great and thanks for quick reply, but what if i need the
st, nd, or rd example......1st, 2nd, 3rd, or 21st, 22nd, 23rd ?? by the
way that worked for me just need to make change when the st, nd, rd are
used.

Excel Date??
 

well i didn't put everything i needed to so heres the rest...i have
cell L1 formatted with =NOW()+5 which creates what todays date is plus
5 days. that will setup a work week date, now in verious cell in the
work sheet for example if L1=NOW()+5 then it looks like this 2/25/2006
now in cell b15 i want the date to be Mon 20th, cell b23 would be Tue
21st all the way down to Friday taking away a day from L1 each time. my
work week starts on Monday and ends on Saturday, so for this week L1 is
2/25/2006 because today is the 20th and if i add 5 days to that i get
2/25/2006. i am using cell L1 as the primary date and want to
distribute that five day work period over other cells kinda like a
schedule.

Excel Date??
 

In B15

=TEXT($L$1-(6-ROW(A1)),"ddd
dd")&IF(AND(DAY($L$1-(6-ROW(A1)))=10,DAY($L$1-(6-ROW(A1)))<=14),"th",
CHOOSE(MOD(DAY($L$1-(6-ROW(A1))),10)+1,"th","st","nd","rd","th","th","th" ,"t
h","th","th"))

copy down to B19

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"slvtenn" wrote in message
oups.com...
well i didn't put everything i needed to so heres the rest...i have
cell L1 formatted with =NOW()+5 which creates what todays date is plus
5 days. that will setup a work week date, now in verious cell in the
work sheet for example if L1=NOW()+5 then it looks like this 2/25/2006
now in cell b15 i want the date to be Mon 20th, cell b23 would be Tue
21st all the way down to Friday taking away a day from L1 each time. my
work week starts on Monday and ends on Saturday, so for this week L1 is
2/25/2006 because today is the 20th and if i add 5 days to that i get
2/25/2006. i am using cell L1 as the primary date and want to
distribute that five day work period over other cells kinda like a
schedule.

Excel Date??
 

the formula above formats the cells in a row like A1, B1, C1, but what
if i wanted to skip some rows for example start with A2 for 1st date
then skip down to A15 for the next date which would look something like
this
(CellA2) Mon 20th




(CellA15) Tue 21st
all the way to Friday. if i use the formula like it is it doesn't work
right if you copy cell A2 after the formula is inserted, into Cell A15.
the 1st cell i copy this formula to works but when you skip down to
Cell A15 after coping Cell A2 and paste the resaults are like this Sun
05th, which i would like it to say Tue 21st.

Excel Date??
 

the formula above formats the cells in a row like A1, B1, C1, but what
if i wanted to skip some rows for example start with A2 for 1st date
then skip down to A15 for the next date which would look something like
this
(CellA2) Mon 20th




(CellA15) Tue 21st
all the way to Friday. if i use the formula like it is it doesn't work
right if you copy cell A2 after the formula is inserted, into Cell A15.
the 1st cell i copy this formula to works but when you skip down to
Cell A15 after coping Cell A2 and paste the resaults are like this Sun
05th, which i would like it to say Tue 21st.

Excel Date??
 

You are chopping and changing every suggestion, so I think there is no more
I can do to help.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"slvtenn" wrote in message
oups.com...
the formula above formats the cells in a row like A1, B1, C1, but what
if i wanted to skip some rows for example start with A2 for 1st date
then skip down to A15 for the next date which would look something like
this
(CellA2) Mon 20th




(CellA15) Tue 21st
all the way to Friday. if i use the formula like it is it doesn't work
right if you copy cell A2 after the formula is inserted, into Cell A15.
the 1st cell i copy this formula to works but when you skip down to
Cell A15 after coping Cell A2 and paste the resaults are like this Sun
05th, which i would like it to say Tue 21st.

Excel Date??
 

well the last reply i posted is the last one, but you know as well as i
do that when programming in excel there are lots of changes done before
the end product is complete. the replys you have given me are great and
i have learned a lot from them i just need this one last thing to
finish doing the things i need the excel program to do, but thanks a
lot for the help you have given me.