Hi everyone,
newbie here so please be gentle!
I'm trying to create a formula which will be used in a cash forecast spreadsheet.
Basically, i have a list of payments down column A in cells A2:A11, and across the top row starting from cell B1 is the date of the first day of that week
I have successfully created a formula in the Tax row cells so that £30 is shown if the week beginning on the above date contains the last working day of the month, i did this by using:
=IF(AND(DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))>=B1,DATE(YEAR(B1),MONTH(B1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),2)-5))
However, rent is always paid on the 5th of every month (as opposed the the last working day of the month as in the above formula), except when this falls on a weekend, in which case it is deducted on the nearest working day before the 5th.
How can i change the formula to reflect this?
I tried:
=IF(AND(DATE(YEAR(B1),MONTH(B1),5)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1),5),2)-5))>=B1,DATE(YEAR(B1),MONTH(B1),5)-(MAX(0,WEEKDAY(DATE(YEAR(B1),MONTH(B1),5),2)-5))
which nearly worked apart from in September, where the the nearest working day to the 5th is the 3rd. This date falls in the week commencing 30.08.10, however the formula calculates that the date rent would be paid in that month is the 5th of August, which is correct i suppose, but it doesn't calculate that the 5th of the following month (September) actually falls in this week - then the next week on my sheet starts w/c 06.09.10 which is obviously after the 03.09.10 and so doesn't show any rent payments either, in effect 'skipping' this payment.
Is there a way for excel to use the w/c date on my sheet, and calculate that the 5th of September falls in the week commending the 30.08.10 and so input £30?
I don't think i'm a million miles away I just think i need some help in fine tuning, but if anyone has a better formula i'd be eternally grateful!
Thanks in advance,
Andy!
Bookmarks