Changing whole year by entring date

[elad]

Hi

I have a sheet where column A5 to Axx is filled with date from 01. jan to 31.dec. I.E.
A5= 01.01.2008
A6= 02.01.2008
A7=03.01.2008
etc
etc

Based on comparing the date the sheets collecects data from other sheets. It works fine, but I want the possibility to change the year by entering i.e 2007 in cell A1 and then the sheets change Cell A5 to 01.01.2007. I know i can use replace function manually, but when i want to simulate/compare betwen years it would be very nice to just enter the year and the sheet would collect new data.

Thanks for any help
elad
Norway

[NBVC]

If you enter the year in A1,

then you could use =DATE($A$1,ROWS($A$1:$A1),1) in A5 copied down to get first day of each month of that year in A1

[elad]

HI, thanks!
Maybe my explaination not was to good, or it is a different use of day/month/year that is confusing

Your formula worked, but It gave me the first date each month like this
A5=01.01.2008 (day/month/year)
A6=01.02.2008 (day/month/year)

I would like the formula to give me 365 new dates by entering the year in A1.
Like this
A5= 01.01.2008 (day/month/year)
A6= 02.01.2008 (day/month/vear)
...
...
A370= 31.12.2008 (day/month/year)

[NBVC]

Then try in A5: =DATE($A$1,1,1)

and in A6, copied down... =$A5+1

[elad]

Thanks! Now it works fine

elad

[elad]

Hi again. The Formula you told me works fine, but I have another problem linked to change date by enter the Year. Every month in the sheet is accumulated with total this month, total so far etc. When it is a leap year i got som problem with number of rows in a month. I have uploaded an example of this. Look in row 72 when year is 2007 and 2008 and I think you understand me. When using 2007 row 72 wil be the first of the next month.....

elad

[NBVC]

You can revise formula in A72 to: =IF(MONTH($A71+1)=MONTH($A70),$A71+1,"")

and revise formula in A76 to: =IF($A72="",$A71+1,$A72+1)

[elad]

EXCELLENT!!

Thanks a lot!

Elad

[elad]

Or almost......

I get som trouble with the Sumproduct formula in cell B 72 in uploaded sheet.
The Formula calculat The A and C column from other worksheets, but when A72 is "" (blank) and there is no criteria for calculating the formula just take the horus totaly that month. I Can solve it by entering i.e xxxx in column A in the linked woorksheet, but that is not very elegant...

So the formula should be anything like this: "Sum A and C column when there is a matching data, but when A72 is "" do not calculate"

I hope dont have any more Questions for a while now...

Thank Elad
Norway

[NBVC]

Simply add an IF() statement to your Sumproduct() formula...

e.g. =IF(A72="","",Sumproduct(........))

you can copy this formula up and/or down for consistency....

[elad]

Thanks again!