# Date Question

#### jonv

##### New Member
I have a workbook that has multiple sheets. Each sheet represents a month, Jan-Dec. Inside of each sheet I have several columns. One of these colums is for the date (11/08/05), and the other is for the day of the week (Tuesday).

I have to go through and populate the date column with the date that corresponds with each day. This is something that I will have to do each year if we decide to use this excel sheet for tracking. Is there anyway to automate this?

I know of the click and drag trick with excel, where you input data, and drag it to other cells and excel will automatically increase the data by 1, but I cannot do this, because there are spaces between each week of the month.

Ex: Cell A1 is blank, A2 says Date, A3-A7 are for Monday-Friday of week one, cells A8-A15 are blank, A16 says Date, A17-A21 are for Monday-Friday of week two. (I thought maybe this would help explain my issue a little better)

I really do not want to sit here and input dates all day. I'd like for a more simple method to do this. Let me know if my question needs any clarification. Any and all help is appreciated. Thanks!

-Jon

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### NBVC

##### Well-known Member
Perhaps you can use simple formulas

Enter the first date in A3, then in A4 enter =A3+1 and copy down to A7, then in A16, enter =A7+9 and copy down to A21 and continue in this pattern for all the weeks.

If all your sheets are set up exactly the same (i.e. a3 to a7 is always week 1, etc.), then Select all sheets, by right-clicking one of the tabs and selecting Select All Sheets. You can delect sheets, by holding the Ctrl key and selecting each tab to deselect.

Then enter the formulas as described above. This will enter the formulas in all selected sheets at once.

#### BrianB

##### Well-known Member
1. You can work on 12 sheets at once if you hold down shift or Ctrl key whilst clicking. (or work on 1 sheet & copy/paste formulas to the others)

2. To add 1 day to a date (let's say in A3) you put the formula
=A3+1
in the cell A4. This formula can then be copied down.
When you get to a breakpoint copy an extra formula and drag or cut/paste that cell to the next position. It retains the formula still referring to what it did before.

Then copy this cell down as before. .etc.

3. Visit each of the 12 sheets and change the date value in cell A3 to change the others below.

If you wish to make the values permanent, reselect all 12 sheets and select column A - Copy then Edit/Paste special/values.

#### jonv

##### New Member
I don't think this will work. A3 won't always be the first date of each month. In the February sheet the first day is Wednesday which is in cell A5. Therefor I would have to recreate the formula for each sheet, correct?

Btw, this is for the year 2006 and above that I will be doing these workbooks.

#### jonv

##### New Member
Ok, is there a way to choose a year, and have excel automatically change the dates based on the year you choose?

Ex: A drop-down list of dates, or even a blank cell that you input 2006, 2007, 2008, etc.

And once the year is selected or entered excel will fill the correct dates?

#### NBVC

##### Well-known Member
Can you must a sample of at least 2 of the sheets using Colo's Html Maker (instructions in link at bottom of page)? And what your expected results are.

For your last post, you can use the DATE function like: =Date(A1,1,1), where the A1 is the reference cell containing the year, the first 1 represents the month and the second 1 represents the year.

#### jonv

##### New Member
January Sheet

Edited by firefytr; Reason: HTML Maker mispost.

#### jonv

##### New Member

What I want is a way for those dates to automatically be filled in. I've already gone through and added them for each month, and I do not want to have to do that for each year. It's just annoying. So is there a way to select a year, and excel will automatically change each date, and make it match with the correct day. So when I choose the year 2010, the 1st of January will show up on the correct day?

I hope this makes sense, lol. I'm having a hard time trying to figure out why my boss just doesn't make the sales team input the date themselves...it takes two seconds. Thanks for all the prompt replies, and all the help. It is greatly appreciated.

#### NBVC

##### Well-known Member
Hi Jonv,

Have a look at this (example for January).

The user inputs the year (in this case in A1), This can be changed but you will have to match the reference in all the formulas.

Then the formula in A3,=IF(WEEKDAY(DATE(\$A\$1,1,1))=2,DATE(\$A\$1,1,1),IF(WEEKDAY(DATE(\$A\$1,1,1))=1,DATE(\$A\$1,1,2),IF(WEEKDAY(DATE(\$A\$1,1,1))=7,DATE(\$A\$1,1,3),"")))
checks whether this is a Monday, if yes it insert the date, if no it checks whether it is a Saturday or Sunday and if so, enter the correct Monday date. If it neither a Monday, Saturday nor a Sunday, it leaves the cell blank. Then cell A4 kicks in.

Cell A4, =IF(WEEKDAY(DATE(\$A\$1,1,1))=3,DATE(\$A\$1,1,1),IF(A3<>"",\$A3+1,""))
checks if the 1st of the month is a Tuesday, and if so, inserts the date here, otherwise it checks if a date exists in A3 and if yes, adds 1 to it.

This is copied down to Friday. (One of these dates will show the first weekday of the month)

Now in Cell A12, it add 3 days to the previous Friday, =IF(\$A\$7<>"",A7+3,"")
A13, adds 1 day to the previous day, =IF(\$A\$7<>"",A12+1,"")
and that is copied to Friday.

Then just copy this last week's formulas, to the next week(s).

Remember, you can then copy this first column to each of the sheets, but you will have to change the 1st number 1 in Date(A1,1,1) function to 2 for February, 3 for March and so on.

Note: I have included error-handling to return blanks when data is not available.
Book1
ABCD
12006January
2
31/2/2006Monday
41/3/2006Tuesday
51/4/2006Wednesday
61/5/2006Thursday
71/6/2006Friday
8
9
10
11
121/9/2006Monday
131/10/2006Tuesday
141/11/2006Wednesday
151/12/2006Thursday
161/13/2006Friday
17
18
19
20
211/16/2006Monday
221/17/2006Tuesday
231/18/2006Wednesday
241/19/2006Thursday
251/20/2006Friday
Sheet1

Hope this all helps. Let me know.

#### jonv

##### New Member
Its awesome! Works like a charm! I have one question. At the end of the January sheet it shows dates for February. January ends on a Tuesday, and for the Wednesday it shows 2/1/06, is there a way to not have that show up? It's nothing major, just one thing I noticed. I want to make this as friendly as possible to our sales team, they're not exactly that computer savvy. And I'm sure it'd confuse some of them having two seperate sections for instances like this.

Thank you so much for helping me, even if that last request cannot be achieved, this is so awesome! I owe ya one. Thanks!!

Replies
1
Views
92
Replies
0
Views
225
Replies
2
Views
211
Replies
6
Views
182
Replies
4
Views
522

1,196,006
Messages
6,012,824
Members
441,731
Latest member
jonceramic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back