Custom fill series?

[Guest]

I need to have a collumn in my spreadsheet which will display every Sunday's
date.

e.g.
25TH JUNE 2006
1ST JULY 2006
8TH JULY 2006
etc...

I also need the "TH" "ST" ND" and "RD" 's to be in superscript.

I am using Excel 2002 (XP) on a Windows XP Home Edition PC.

Thanks,
Martin

 

[Don Guillett]

Here is a formula for an adjacent column. You could use a macro to ask for a
date and fill in


=DAY(D7)&LOOKUP(DAY(D7),{1,2,3,4},{"st","nd","rd","th"})&TEXT(D7," mmm")

 

[Guest]

Wow - that is just what I wanted!

Thank you so much.

Martin

 

[RagDyeR]

You can enter a Sunday date, and the right click and drag down as needed.
When the mouse is released, the bottom choice in the option window is
'Series'.

Click on that, and enter 7 in the 'StepValue' box, then <OK>.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Wow - that is just what I wanted!

Thank you so much.

Martin

 

[Franz Verga]

Nel post *martinkorner* ha scritto:

I need to have a collumn in my spreadsheet which will display every
Sunday's date.

e.g.
25TH JUNE 2006
1ST JULY 2006
8TH JULY 2006
etc...

I also need the "TH" "ST" ND" and "RD" 's to be in superscript.

I am using Excel 2002 (XP) on a Windows XP Home Edition PC.

Thanks,
Martin

Hi Martin,

I suppose you have the real dates in column A, starting from A1 (in A2 type
=A1+7 and copy down). In B1 copy this formula:

=DAY(A1)&IF(DAY(A1)=1,"st",IF(DAY(A1)=2,"nd",IF(DAY(A1)=3,"rd","th")))&"
"&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy")

and fill down.

IMHO there is no simple way to have st, nd, rd and th as superscript, maybe
with a macro, but I can't support you that way.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Don Guillett]

If you liked the formula you will really like this. Position the cursor
where you want the dates to start. Execute the macro. You will be asked for
the first date and the number of weeks desired.

Sub datefillcustom()
ac = ActiveCell.Column
ar = ActiveCell.Row - 1
md = InputBox("Enter date like 01/28/06")

If Not IsDate(md) Then
MsgBox "Start Over"
Exit Sub
End If

mdate = DateSerial(Year(md), Month(md), Day(md))
For i = 1 To InputBox("Enter number of weeks desired")
Select Case Day(mdate)
Case 1: x = "st"
Case 2: x = "nd"
Case 3: x = "rd"
Case Is > 3: x = "th"
Case Else
End Select
Cells(ar + i, ac).Value = Day(mdate) & x & Format(mdate, " mmm, yyyy")
mdate = mdate + 7
Next i

 

[Don Guillett]

If you want the month in upper case use this
Cells(ar + i, ac).Value = Day(mdate) & x & UCase(Format(mdate, " MMM,
yyyy"))

 

[Guest]

Thanks to everyone for all your help.

I started by using RD's method. Then tried Don's method, then modified it to
get Franz's method!

Thansk again,
Martin

 

[David]

For me, all offerings failed to provide proper ordinal for days 21, 22, 23,
31

 

[David]

Need to modify Case statements:

Select Case Day(mdate)
Case 1, 21, 31: x = "st"
Case 2, 22: x = "nd"
Case 3, 23: x = "rd"
Case Else: x = "th"
End Select

 

[Don Guillett]

Thanks for catching that. Of course, this is, by far, the easiest way to get
it done.

 

[Guest]

Right then...I've got a solution!!

I also realised the problem with 21, 22, 23 and 31.

So I made this formula...It's probably far longer than it needed to be, but
this works, so I'm happy with it:

 

[Don Guillett]

modify my macro

 

[Franz Verga]

Maybe this is shorter than yours:

=DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"st",IF(OR(DAY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)=3,DAY(A1)=23),"rd","th")))&"
"&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy")

but it works also with 21, 22, 23 and 31

Right then...I've got a solution!!

I also realised the problem with 21, 22, 23 and 31.

So I made this formula...It's probably far longer than it needed to
be, but
this works, so I'm happy with it:

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

=DAY(A60)&LOOKUP(DAY(A60),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31},{"st","nd","rd","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","th","st","nd","rd","th","th","th","th","th","th","th","st"})&TEXT(A60,"
mmmm")&TEXT(A60," yyyy")

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

It basically specifies the suffix for each individual date.

Martin

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

That gives me a small square between the date and the month.

Thanks,
Martin

 

[Franz Verga]

It's because Outlook Express cut the formula in the wrong points. Try
copying each line separately:

=DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"st",
IF(OR(DAY(A1)=2,DAY(A1)=22),"nd",IF(OR(DAY(A1)=3,DAY(A1)=23),"rd","th")))
&" "&UPPER(TEXT(A1,"mmmm"))&" "&TEXT(A1,"yyyy")

That gives me a small square between the date and the month.

Thanks,
Martin

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy