Creating date sequence in Excel 97

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Creating date sequence in Excel 97

  1. #1
    New Member
    Join Date
    Dec 2003
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Creating date sequence in Excel 97

     
    I've run out of ideas about how to create a date sequence for a work rota. It's a recurring weekly pattern of five dates and then two dates doubled, e.g.

    Jul/01/04
    Jul/02/04
    Jul/03/04
    Jul/04/04
    Jul/05/04
    Jul/06/04
    Jul/06/04
    Jul/07/04
    Jul/07/04

    This is followed by a gap, and then the pattern repeats ad infinitum. Currently I do this manually, and it's terribly tedious.

    Any suggestions gratefully received.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,447
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating date sequence in Excel 97

    Try to prevent wild guesses on the part of the would be helpers by specifying the rule how you create the sequence. Anything special about the start date? Do the week end dates play any special role, etc.?

  3. #3
    New Member
    Join Date
    Dec 2003
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating date sequence in Excel 97

    No, it's very simple. The first date is a Monday, the last two are Saturday and Sunday split into two shifts.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,447
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating date sequence in Excel 97

    Quote Originally Posted by sgmacs
    No, it's very simple. The first date is a Monday, the last two are Saturday and Sunday split into two shifts.
    1-July-04 is a Thursday...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Date Seq
    2
    Mon 5-Jul-04
    3
    Tue 6-Jul-04
    4
    Wed 7-Jul-04
    5
    Thu 8-Jul-04
    6
    Fri 9-Jul-04
    7
    Sat 10-Jul-04
    8
    Sat 10-Jul-04
    9
    Sun 11-Jul-04
    10
    Sun 11-Jul-04
    11
    Mon 12-Jul-04
    12
    Tue 13-Jul-04
    13
    Wed 14-Jul-04
    14
    Thu 15-Jul-04
    15
    Fri 16-Jul-04
    16
    Sat 17-Jul-04
    17
    Sat 17-Jul-04
    18
    Sun 18-Jul-04
    19
    Sun 18-Jul-04
    Sheet1

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in A3 is:

    =A2+IF(OR(WEEKDAY(LOOKUP(9.99999999999999E+307,$A$2:A2),2)={6,7}),0,1)+(A2=A1)

  5. #5
    New Member
    Join Date
    Dec 2003
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating date sequence in Excel 97

    That's great. Thanks very much.

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating date sequence in Excel 97

      
    Hi sgmacs:

    Or you may also try ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - y031221h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    2
    05-Jul-04
    3
    06-Jul-04
    4
    07-Jul-04
    5
    08-Jul-04
    6
    09-Jul-04
    7
    10-Jul-04
    8
    10-Jul-04
    9
    11-Jul-04
    10
    11-Jul-04
    11
    12-Jul-04
    12
    13-Jul-04
    13
    14-Jul-04
    14
    15-Jul-04
    15
    16-Jul-04
    16
    17-Jul-04
    17
    17-Jul-04
    18
    18-Jul-04
    19
    18-Jul-04
    20
    19-Jul-04
    21
    20-Jul-04
    22
    21-Jul-04
    23
    22-Jul-04
    24
    23-Jul-04
    25
    24-Jul-04
    Sheet2 (2)

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    Cell A2 houses the starting date, then the formula in cell A3 is ...

    =A2+(MOD(A2,7)>1)+(A2=A1)

    and this is then copied down to as many cells as desired.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com