Changing Date

[shorie7]

Hello All...

I was wondering if someone could help me. I am new to excel, and has designed a worksheet to help me with my annual tax return as I am a self employed driver. I thought I had got my worksheet working fine, but its not the case. Its difficult to explain what I am after, but here goes. What I am looking for is this....

I have created a work sheet for 5-4-2014, so that when I enter the date (5-4-14) into page 1, cell A9, it lists all the Saturdays in rows (A11:A63) on page 1 for the entire year.

There are 14 pages in my workbook, the first page is where I enter all my wages, expenses etc, and the other thirteen represent the months of the year from April end to April start.

When I enter information into page 1, I have set up excel so it copies all the information from page 1 to the relevant month in the workbook. So if I enter something for 23rd August on page one, if I click on the page named "August" along the bottom, the information is in the relevant place.

This is working fine on this worksheet, however, this is where the trouble begins.

I wanted to use this original worksheet every year.
If I change the date to 5-4-15 in Sheet 1, A9, (which I will need for next year), October now has a date missing, and the month of November now has a line for October in row 10.

Or if you put 5-4-16 in Sheet 1 A9, when you click on May on the bottom, the wrong information is in line 10. <o></o>
It has the 30th of April's information in row 10 for the month of May.
<o></o>
So with the date at 5-4-16, May is wrong, August is wrong, November is wrong, and so is January.<o></o>

I want the months along the bottom to work like they do now and correspond to the information entered in Sheet 1 to the respective months along the bottom.
I now have to alter the entire sheet every year for it to calculate correctly.<o></o>
Its as though the change in the year date on page 1 A9 has altered the entire workbook so it no longer works correctly.
I am probably not making sense, and its so frustrating trying to explain.I need all the months in the book to correspond to page one, REGARDLESS of what year is entered on page 1, if this is at all possible.

Please contact me if anyone can offer advice. I have searched the net everywhere but all I can find are date formulas, whereas I (think) i need some sort of copy formula to copy whats in page one to the other pages to their correct months.
If anyone wants a copy of my original workbook to see my problem I can send one through my email to you.
Just forward your email address with a quick note asking for it so you can actually see my problem at work.

Thank you for any advice you can offer. I thought I had got it cracked, and was well pleased with all my efforts of studying the net to get this far.
If I can just sort out this part it will be perfect.

a very frustrated
Shorie7

 

[ukmikeb]

Hi

This formula in cell A11 :-

=IF(ROWS($A$11:A11)=1,$A$9+CHOOSE(WEEKDAY($A$9),6,5,4,3,2,1,0),IF($A$11+7*(ROWS($A$11:$A11)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A11)-1)))

will replicate all the Saturday dates for whatever year is entered in A9.

hth

 

[ukmikeb]

ps and drag the formula down to A63

 

[shorie7]

Hello,

I copied your formula and pasted it into A11 as you instructed. This changed th date to 5th April 2014.
When I pasted it into A12, it made this date 5th April 2014 also.

The problem I am having is not the dates on page 1...but the way the dates get changed into the corresponding months along the bottom in the workbook.

I need the dates and months from page 1 to get transferred to the correct months and dates in the rest of the workbook. Is there anyway I can attach a copy of my workbook to this thread so you can see what I am trying to achieve?
I am probably not explaining it correctly. I know what I want it to do, but just cant figure out HOW to get excel to do it. I know its a powerful program, so I should imagine it CAN be done... but by someone with more knowledge of the program than what I have.
Thanks for your advice anyhow, and if you can assist further I will be greatly appreciative.
shorie7

 

[ukmikeb]

Hi

What version of Excel are you using? Have you got the Analysis Toolpak installed?

What formula do you use to convert the dates into months?

My signature lists a couple of routines to display a section of the spreadsheet.

 

[shorie7]

Hello,

I am using 2007 excel. I dont know what the 'analysis toolpack' is.
I am not using a formula to convert the dates into months. What I have done is this...

On Page 1 in A9 i enter the date (ie 5-4-14)

Then on page 1 in A11 I put the formula =A9+IF(WEEKDAY(A9)=7,14,7)-WEEKDAY(A9). This gives me the following Saturday after the date entered into A9

Then on page 1 in A12 i put the formula =A11+7
Then on page 1 in A13 I put the formula =A12+7 and so on all the way down to A63. This gives me every Saturdays date from the one I entered into A9 for the rest of the year.

On the months tabs along the bottom, for the corresponding month I have got excel to basically copy the information from page 1 into the appropiate line in the month.

Ie
If on page 1 in A32 is the 6th September, In the September month along the bottom, I have asked it to copy all the information from this LINE into the correct date in the MONTH tab along the bottom.

Maybe this is what I am doing wrong, for as the dates change every year on page one, the MONTHS tab along the bottom is still copying from the SAME place...even though the date has now changed. (It may be the 13 of September In A32 next year, but it will still copy from A32).

I want to be able to put my date in on Page 1 in A9. All the dates will then copy correctly on page 1.
But then I need excel to RECOGNISE the dates from page one, and COPY the information from page one into the CORRECT dates/months along the bottom, even if i DO change the year date in A9.

If you need to see my workbook, please mail me on (e-mail address removed) and I will be happy to send you a copy to see where the problem lies.
Perhaps it may be clearer if you are looking at it, rather than have me not explain it correctly.

The workbook works fine for this year, as this is how I programmed it to work... to work for this year.

But next year it will be wrong, and even more so the year after that.

I just dont want to have to do another complete workbook every 5th of April. This one took me ages.
There is probably an easier way, as the program looks very powerful with calculations.
I am just at a loss on how to get it done. (I'm a lorry driver after all lol!)


I dont understand what you mean by your signature listing routines my friend.

Again, if you can assist, I will be truly grateful.
Thank you in advance.
shorie7

 

[ukmikeb]

I have created a work sheet for 5-4-2014, so that when I enter the date (5-4-14) into page 1, cell A9, it lists all the Saturdays in rows (A11:A63) on page 1 for the entire year.

There are 14 pages in my workbook, the first page is where I enter all my wages, expenses etc, and the other thirteen represent the months of the year from April end to April start.

When I enter information into page 1, I have set up excel so it copies all the information from page 1 to the relevant month in the workbook. So if I enter something for 23rd August on page one, if I click on the page named "August" along the bottom, the information is in the relevant place.

This is working fine on this worksheet, however, this is where the trouble begins.

I wanted to use this original worksheet every year.
If I change the date to 5-4-15 in Sheet 1, A9, (which I will need for next year), October now has a date missing, and the month of November now has a line for October in row 10.

Or if you put 5-4-16 in Sheet 1 A9, when you click on May on the bottom, the wrong information is in line 10. <o></o>
It has the 30th of April's information in row 10 for the month of May.
<o></o>
So with the date at 5-4-16, May is wrong, August is wrong, November is wrong, and so is January.<o></o>

What formulae do you use to relate the dates on the first sheet to dates on each monthly sheet.

I copied your formula and pasted it into A11 as you instructed. This changed th date to 5th April 2014.
When I pasted it into A12, it made this date 5th April 2014 also.

You need to drag the formula down by clicking the black square on the bottom right hand corner of cell A11.

Having said that your current formulae are sufficient.

I am using 2007 excel. I dont know what the 'analysis toolpack' is.
I am not using a formula to convert the dates into months. What I have done is this...

On Page 1 in A9 i enter the date (ie 5-4-14)

Then on page 1 in A11 I put the formula =A9+IF(WEEKDAY(A9)=7,14,7)-WEEKDAY(A9). This gives me the following Saturday after the date entered into A9

Then on page 1 in A12 i put the formula =A11+7
Then on page 1 in A13 I put the formula =A12+7 and so on all the way down to A63. This gives me every Saturdays date from the one I entered into A9 for the rest of the year.

On the months tabs along the bottom, for the corresponding month I have got excel to basically copy the information from page 1 into the appropiate line in the month.

Ie
If on page 1 in A32 is the 6th September, In the September month along the bottom, I have asked it to copy all the information from this LINE into the correct date in the MONTH tab along the bottom.

Maybe this is what I am doing wrong, for as the dates change every year on page one, the MONTHS tab along the bottom is still copying from the SAME place...even though the date has now changed. (It may be the 13 of September In A32 next year, but it will still copy from A32).

I want to be able to put my date in on Page 1 in A9. All the dates will then copy correctly on page 1.
But then I need excel to RECOGNISE the dates from page one, and COPY the information from page one into the CORRECT dates/months along the bottom, even if i DO change the year date in A9.

If you need to see my workbook, please mail me on rachel.anthony@blueyonder.co.uk and I will be happy to send you a copy to see where the problem lies.
Perhaps it may be clearer if you are looking at it, rather than have me not explain it correctly.

The workbook works fine for this year, as this is how I programmed it to work... to work for this year.

But next year it will be wrong, and even more so the year after that.

I just dont want to have to do another complete workbook every 5th of April. This one took me ages.
There is probably an easier way, as the program looks very powerful with calculations.
I am just at a loss on how to get it done. (I'm a lorry driver after all lol!)


I dont understand what you mean by your signature listing routines my friend.

Don't worry about the Analysis Toolpak for the time being, I didn't realise that you hadn't dragged my formula down from A11.

I and many other members of this forum will not respond directly through Email or Private Message in order to solve a problem since it is contrary to the Rules of the Forum and ultimately does not help other members who may have a similar problem in the future.

As far as the routines are concerned, they can be accessed for download by clicking either "Mr Excel HTML Maker" or "Excel Jeanie" in the last line of this post.

 

[shorie7]

"What formulae do you use to relate the dates on the first sheet to dates on each monthly sheet."

I haven't set up a formula on the first sheet to relate the the dates on the first sheet to the rest of the book. Maybe this is where the problem lies.

I tried to get each month to "look" for 'April' or 'May' etc and copy the lines from page one into the respective months but got nowhere.

I dont know excel, I hardly use it.
But I needed this sheet and other sheets I have created to keep my financial books or driving times/working hours in order, and after working a 14 hour night shift, I dont get a lot of time to study it.
I have been able to try and find out this week as I am on holiday for a week...of which I have spent most of it trying to find a solution (some holiday!).

Again, thanks in advance for any help
Mark

 

[ukmikeb]

Here is a way to set up the dates on the sheets.

On your first sheet :-

Excel Workbook
	A
9	05/04/2014
10
11	05/04/2014
12	12/04/2014
13	19/04/2014
14	26/04/2014
15	03/05/2014
16	10/05/2014
17	17/05/2014
18	24/05/2014
19	31/05/2014
20	07/06/2014
Shorie1

Excel 2007

Cell Formulas
Range	Formula
A11	=IF(ROWS($A$11:A11)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A11)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A11)-1)))
A12	=IF(ROWS($A$11:A12)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A12)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A12)-1)))
A13	=IF(ROWS($A$11:A13)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A13)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A13)-1)))
A14	=IF(ROWS($A$11:A14)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A14)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A14)-1)))
A15	=IF(ROWS($A$11:A15)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A15)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A15)-1)))
A16	=IF(ROWS($A$11:A16)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A16)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A16)-1)))
A17	=IF(ROWS($A$11:A17)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A17)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A17)-1)))
A18	=IF(ROWS($A$11:A18)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A18)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A18)-1)))
A19	=IF(ROWS($A$11:A19)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A19)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A19)-1)))
A20	=IF(ROWS($A$11:A20)=1,$A$9+CHOOSE(WEEKDAY($A$9,2),5,4,3,2,1,0,6),IF($A$11+7*(ROWS($A$11:$A20)-1)>EDATE($A$9,12),"",$A$11+7*(ROWS($A$11:$A20)-1)))

On your sheet for April :-

Excel Workbook
	A
9	01/04/2014
10
11	05/04/2014
12	12/04/2014
13	19/04/2014
14	26/04/2014
15
16
Shorie April 2014

Excel 2007

Cell Formulas
Range	Formula
A9	=DATE(YEAR(Shorie1!$A$9),4,1)
A11	=IFERROR(SMALL(IF(MONTH(Shorie1!A$11:A$63)=MONTH($A$9),Shorie1!$A$11:$A$63),ROWS($A$11:$A11)),"")
A12	=IFERROR(SMALL(IF(MONTH(Shorie1!A$11:A$63)=MONTH($A$9),Shorie1!$A$11:$A$63),ROWS($A$11:$A12)),"")
A13	=IFERROR(SMALL(IF(MONTH(Shorie1!A$11:A$63)=MONTH($A$9),Shorie1!$A$11:$A$63),ROWS($A$11:$A13)),"")
A14	=IFERROR(SMALL(IF(MONTH(Shorie1!A$11:A$63)=MONTH($A$9),Shorie1!$A$11:$A$63),ROWS($A$11:$A14)),"")
A15	=IFERROR(SMALL(IF(MONTH(Shorie1!A$11:A$63)=MONTH($A$9),Shorie1!$A$11:$A$63),ROWS($A$11:$A15)),"")
A16	=IFERROR(SMALL(IF(MONTH(Shorie1!A$11:A$63)=MONTH($A$9),Shorie1!$A$11:$A$63),ROWS($A$11:$A16)),"")

Then for the other months in the current year you only have to alter the month number in A9. And for the months in the following year add 1 to the year held in cell a9 of your first sheet.

hth