# IF and Date functions

1. ## IF and Date functions

Hi,
I need some help writing the right formula for my Cash flow forecast.
On the attached spread sheet,
Column A - Tenant's name
B - Tenancy start date
C - Tenancy end date
D - Monthly rent
E to T - Weekly dates

Rent is payable monthly from the first day of the tenancy. Based on the rent due dates, I want the rent amount to be slotted into the right date column in E-T.
For example, tenant A's first instalment will be due on 30/7/20 and therefore, £706 should be in E5 as it falls in the week commencing 27/07/2020. The 2nd instalment will be due on 30/08/2020 and therefore should be in column I.

Thank you
Mathanki

2. ## Re: IF and Date functions

Put this formula in E2, the copy down and across the table.

=IF(DATE(YEAR(E\$1),MONTH(E\$1),DAY(\$B2))-MOD(DATE(YEAR(E\$1),MONTH(E\$1),DAY(\$B2))-2,7)=E\$1, \$D2, "")

3. ## Re: IF and Date functions

Thank you so much! It works!!

4. ## Re: IF and Date functions

I think the formula needs a little tweaking - it misses the February 2021 payment likely because there aren't at least 30 days in that month. If B2 is 7/28/2020, then it calculates correctly, appearing in the week of 2/22/2021.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1