Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Agreement of day and date (MS Excel 2000 SP3)

    I have an annual worksheet to record bookings on a daily basis. Basically one row, one day in column A, its date in column B and an event in column C - not always one event but I want to keep this simple! I'm only a five-eighths!

    How the sheet was set up I don't know but a sheet is now required for 2008. So I copied and pasted the 2007 sheet and renamed it 2008. I then looked at the day/date arrangement and wondered how to quickly and efficiently change 365/366 (2008 is a Leap Year) days rows to correspond with their correct dates. I tried Autofill but that didn't work. A partial sample.is attached.

    Any ideas gratefully received.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Agreement of day and date (MS Excel 2000 SP3)

    Why not simply enter 1/1/2008 in a cell, then fill down? You can format the cells any way you like.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Agreement of day and date (MS Excel 2000 SP3)

    A couple of questions for you:

    1- Do you really want the Day of the week, Month, and Ordinal day number is three separate columns?

    2- Does the day number have to be ordinal (with the st, nd, rd, or th after the number)?

    If the answer to those questions are no, then does the attached get you close enough?
    Attached Files Attached Files
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Agreement of day and date (MS Excel 2000 SP3)

    Separate coumns? Definitely not! Your way is aok by me. How is it done or do I have to work it out for myself?

    Formula! I was obsessed with filldown and the like.

    Thank you very much.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Agreement of day and date (MS Excel 2000 SP3)

    I put the the Jan 1 date of the desired year in row 1 ( ie 1/1/2007). I formatted this cell with the custom format 'yyyy' so that the cell displays only the year. In row two, I put a formula that referes to the date in row 1. In row 3 I put an IF formula that checks to see if the year of the date in the cell above plus one day is still equal to the year of the date in the absolute reference to the cell in row 1. If they are equal, then the formula returns the date of the cell above plus one day and if no the it returns null (this is to not display a date for day 366 if it is not a leap year). I then copied the formula down for 365 rows which gave me 366 possible dates. I then formatted the cells in row 2 through row 367 with the custom format 'ddd mmm d' which displays the day abbreviation, the month abbreviation and the day of the month number.
    Legare Coleman

Posting Permissions

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