Agreement of day and date (MS Excel 2000 SP3)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote