+ Reply to Thread
Results 1 to 16 of 16

Custom fill series?

  1. #1
    martinkorner
    Guest

    Custom fill series?

    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

  2. #2
    Don Guillett
    Guest

    Re: Custom fill series?

    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")

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "martinkorner" <[email protected]> wrote in message
    news:[email protected]...
    >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




  3. #3
    martinkorner
    Guest

    Re: Custom fill series?

    Wow - that is just what I wanted!

    Thank you so much.

    Martin

    "Don Guillett" wrote:

    > 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")
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "martinkorner" <[email protected]> wrote in message
    > news:[email protected]...
    > >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

    >
    >
    >


  4. #4
    RagDyeR
    Guest

    Re: Custom fill series?

    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!
    =====================================================

    "martinkorner" <[email protected]> wrote in message
    news:[email protected]...
    Wow - that is just what I wanted!

    Thank you so much.

    Martin

    "Don Guillett" wrote:

    > 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")
    >
    > --
    > Don Guillett
    > SalesAid Software
    > [email protected]
    > "martinkorner" <[email protected]> wrote in message
    > news:[email protected]...
    > >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

    >
    >
    >




  5. #5
    Franz Verga
    Guest

    Re: Custom fill series?

    Nel post news:[email protected]
    *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



  6. #6
    Don Guillett
    Guest

    Re: Custom fill series?

    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
    SalesAid Software
    [email protected]
    "martinkorner" <[email protected]> wrote in message
    news:[email protected]...
    >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




  7. #7
    Don Guillett
    Guest

    Re: Custom fill series?

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

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Don Guillett" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > SalesAid Software
    > [email protected]
    > "martinkorner" <[email protected]> wrote in message
    > news:[email protected]...
    >>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

    >
    >




  8. #8
    martinkorner
    Guest

    Re: Custom fill series?

    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

  9. #9
    David
    Guest

    Re: Custom fill series?

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

    --
    David

    =?Utf-8?B?bWFydGlua29ybmVy?= wrote

    > 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



  10. #10
    David
    Guest

    Re: Custom fill series?

    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

    --
    David

    Don Guillett wrote

    > 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
    >




  11. #11
    Don Guillett
    Guest

    Re: Custom fill series?

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

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "David" <[email protected]> wrote in message
    news:%[email protected]...
    > 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
    >
    > --
    > David
    >
    > Don Guillett wrote
    >
    >> 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
    >>

    >
    >




  12. #12
    martinkorner
    Guest

    Re: Custom fill series?

    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

    "David" wrote:

    > For me, all offerings failed to provide proper ordinal for days 21, 22, 23,
    > 31
    >
    > --
    > David
    >
    > =?Utf-8?B?bWFydGlua29ybmVy?= wrote
    >
    > > 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

    >
    >


  13. #13
    Don Guillett
    Guest

    Re: Custom fill series?

    modify my macro

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "martinkorner" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >
    > "David" wrote:
    >
    >> For me, all offerings failed to provide proper ordinal for days 21, 22,
    >> 23,
    >> 31
    >>
    >> --
    >> David
    >>
    >> =?Utf-8?B?bWFydGlua29ybmVy?= wrote
    >>
    >> > 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

    >>
    >>




  14. #14
    Franz Verga
    Guest

    Re: Custom fill series?

    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

    martinkorner wrote:
    > 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
    >
    > "David" wrote:
    >
    >> For me, all offerings failed to provide proper ordinal for days 21,
    >> 22, 23, 31
    >>
    >> --
    >> David
    >>
    >> =?Utf-8?B?bWFydGlua29ybmVy?= wrote
    >>
    >>> 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


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



  15. #15
    martinkorner
    Guest

    Re: Custom fill series?

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

    Thanks,
    Martin

    "Franz Verga" wrote:

    > Maybe this is shorter than yours:
    >
    > =DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"st",IF(OR(D

    AY(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
    >
    > martinkorner wrote:
    > > 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,1

    5,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
    > >
    > > "David" wrote:
    > >
    > >> For me, all offerings failed to provide proper ordinal for days 21,
    > >> 22, 23, 31
    > >>
    > >> --
    > >> David
    > >>
    > >> =?Utf-8?B?bWFydGlua29ybmVy?= wrote
    > >>
    > >>> 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

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


  16. #16
    Franz Verga
    Guest

    Re: Custom fill series?

    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")

    martinkorner wrote:
    > That gives me a small square between the date and the month.
    >
    > Thanks,
    > Martin
    >
    > "Franz Verga" wrote:
    >
    >> Maybe this is shorter than yours:
    >>
    >> =DAY(A1)&IF(OR(DAY(A1)=1,DAY(A1)=21,DAY(A1)=31),"st",IF(OR(D

    > AY(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
    >>
    >> martinkorner wrote:
    >>> 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,1

    > 5,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
    >>>
    >>> "David" wrote:
    >>>
    >>>> For me, all offerings failed to provide proper ordinal for days 21,
    >>>> 22, 23, 31
    >>>>
    >>>> --
    >>>> David
    >>>>
    >>>> =?Utf-8?B?bWFydGlua29ybmVy?= wrote
    >>>>
    >>>>> 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

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


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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1