Custom fill series?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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")
 
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
 
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
Click to expand...


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
 
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
 
If you want the month in upper case use this
Cells(ar + i, ac).Value = Day(mdate) & x & UCase(Format(mdate, " MMM,
yyyy"))
 
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
 
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
 
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:
 
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
Click to expand...

--
(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
 
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
Click to expand...

--
(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
 
Back
Top