Results 1 to 6 of 6

Creating date sequence in Excel 97

This is a discussion on Creating date sequence in Excel 97 within the Excel Questions forums, part of the Question Forums category; I've run out of ideas about how to create a date sequence for a work rota. It's a recurring weekly ...

  1. #1
    New Member
    Join Date
    Dec 2003
    Posts
    3

    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
    65,217

    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

    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
    65,217

    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

    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

    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

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