Hi

Sorry this is quite complicated to explain.
I am trying to create a month by month budget and want to create a master
workbook that I can use every year. The problem I have is that I need to show
the day, date, month and year (format ddd dd-mmm-yy) on each row and each
month on a separate tab (which I know is not ideal but I need to have it like
that)

How can I create a master file that automatically updates the date depending
on the financial year (which starts on the first Monday of April and ends 52
weeks later so starts on a different date each year)

for example: 2010/11

tab = April
date outgoings
Mon 05-Apr-10 £250.00
Tue 06-Apr-10 £190.00
etc

It seemed easy at first, just using the previous date+1, but I stumbled on
February when it's a leap year because of the extra day (it put Feb 29th on
March's tab, March 31st on April's tab etc), and both Aprils because they
weren't the same number of days each time.

Is there an easy way of doing this? I am not very good with vba or macros
and want to make it as simple as possible, and the minimum amount of update
each year as possible.

I am using Excel 2003

Thanks for any help you can give

Lets say you input the year you want in A1 of sheet "April". To calculate
first monday of April, in cell A2:

=DATE(A1,4,SUMPRODUCT(--(WEEKDAY(DATE(A1,4,ROW($A$1:$A$7)),2)=1),ROW($A$1: $A$7)))

In cell A3, input:
=IF(A2="","",IF(MONTH(A2+1)=MONTH(A2),A2+1,""))
Copy down at least 31 cells.

On sheet May, cell A2:
=MAX('April'!A2:A32)+1
cell a3, input:
=IF(A2="","",IF(MONTH(A2+1)=MONTH(A2),A2+1,""))

Continue this pattern until you get to 2nd April sheet. In A2 of that sheet,
input
=IF(MAX(March!$A$2:$A$32)=DATE(April!A1+1,4,SUMPRO DUCT(--(WEEKDAY(DATE(April!A1+1,4,ROW($A$1:$A$7)),2)=1),R OW($A$1:$A$7))),"",MAX(March!$A$2:$A$32)+1)

In A3:
=IF(A2="","",IF(A2+1=DATE(April!$A$1+1,4,SUMPRODUC T(--(WEEKDAY(DATE(April!$A$1+1,4,ROW($A$1:$A$7)),2)=1) ,ROW($A$1:$A$7))),"",A2+1))


Your workbook should now be setup to display all the dates from 1 fiscal
year, going from first Monday in April to just before the first monday in
april of next year. To change years, all you need to do is change the year in
A1 of sheet April.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tuxla" wrote:

Hi

Sorry this is quite complicated to explain.
I am trying to create a month by month budget and want to create a master
workbook that I can use every year. The problem I have is that I need to show
the day, date, month and year (format ddd dd-mmm-yy) on each row and each
month on a separate tab (which I know is not ideal but I need to have it like
that)

How can I create a master file that automatically updates the date depending
on the financial year (which starts on the first Monday of April and ends 52
weeks later so starts on a different date each year)

for example: 2010/11

tab = April
date outgoings
Mon 05-Apr-10 £250.00
Tue 06-Apr-10 £190.00
etc

It seemed easy at first, just using the previous date+1, but I stumbled on
February when it's a leap year because of the extra day (it put Feb 29th on
March's tab, March 31st on April's tab etc), and both Aprils because they
weren't the same number of days each time.

Is there an easy way of doing this? I am not very good with vba or macros
and want to make it as simple as possible, and the minimum amount of update
each year as possible.

I am using Excel 2003

Thanks for any help you can give