Date Question

[jonv]

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

 

[NBVC]

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]

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]

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]

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?

This is what gives me this idea: Click Here

 

[NBVC]

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]

January Sheet

Edited by firefytr; Reason: HTML Maker mispost.

 

[jonv]

I'm not sure if this will help you any?

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]

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
	A	B	C	D
1	2006	January
2
3	1/2/2006	Monday
4	1/3/2006	Tuesday
5	1/4/2006	Wednesday
6	1/5/2006	Thursday
7	1/6/2006	Friday
8
9
10
11
12	1/9/2006	Monday
13	1/10/2006	Tuesday
14	1/11/2006	Wednesday
15	1/12/2006	Thursday
16	1/13/2006	Friday
17
18
19
20
21	1/16/2006	Monday
22	1/17/2006	Tuesday
23	1/18/2006	Wednesday
24	1/19/2006	Thursday
25	1/20/2006	Friday
Sheet1

Hope this all helps. Let me know.

 

[jonv]

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!!