If Formula to Generate Result if 1 date is 'X' days on from Second Date

Hi All,

I am responsible for submitting cash flow forecasts to my Head Office on a weekly basis. Unfortunately there is no standard document operated by the group and (having worked in Head Office previously) I know there isn't any cashflow spreadsheet used by any of the subsidiaries that are up to standard. I constructed a spreadsheet that improved on what the group had allowed subsidiaries to operate so far, including variance analysis etc. The only problem with the spreadsheet I created was that I had to manually insert the future expenses in each weekly column and this obviously lends itself to errors and omissions. What I'm trying to do now is create a 'Master Data' sheet that holds all types of expenses e.g. salaries, where I can assign a payment amount e.g. salaries at $50,000, assign a frequency e.g. salaries paid on a monthly basis, and all this information would then populate the cashflow sheet.

The problem I'm having (and the reason for this post) relates to the creation of the 'Master Data' sheet, specifically the generation of cost information based on the frequency option. If this all sounds too complicated, hopefully an example (referencing the attached) will lend clarity.

In column B I have an expense type.
You can ignore column C & D for now
In column E I have a drop down list to select the frequency of the payment
Column F is used only when the payment frequency is selected as 'Weekly'
Column G - I is used only when the payment frequency is 'Monthly' - the date entered should be the first date of payment in the year
Column J automatically generates a date from the options selected in columns G - I, but is also used when the payment frequency is selected as 'Annually' or 'One-Off' (although when 'Annually' or 'One-Off' is selected then a date has to be manually entered here).
Column M is used for the expense amount.

Once a payment frequency has been selected, a date has been inserted and a payment amount has been entered, the calendar (columns O - NP) should be populated accordingly. This is facilitated by the nested IF formula in these columns.

The issue I'm having is trying to figure in an IF formula for the following options; Fortnightly', 'Bi-Monthly', 'Quarterly' and 'Bi-annually'.

Taking the example in the spreadsheet in it's downloadable format, I've said salaries are payable on 04 January 2016 and the approximate cost is $25,000. You will see in the calendar that this amount shows in column R (04-Jan) as well as the fourth of every other month for the remainder of the year.
I would like the same to apply to 'Quarterly ' options. If I change the salary frequency to quarterly, I would like the nested IF function to show salaries payable on 04-Jan, 04-Apr, 04-Jul & 04-Oct. How would I go about doing this? And also how could I achieve the same results for fortnightly options etc.

I haven't attempted to work these into your already long formula, mainly 'cos my screen is very small...

However, change E5 and see if the appropriate yellow-shaded line changes to suit your needs

You're welcome and thanks for the Rep. It took me a while to see where to go. Just as well you can't see the deleted rows where it didn't quite work!!

I can understand that it would have taken some time, but you managed it in a much quicker fashion than I would have though.

I have three other points:

- In relation to the fortnightly formula, it would seem that I have no control over which fortnightly rotation the expense should fall into. Your formula seems to assume all fortnightly expenses will be incurred in the second week of the year and every two weeks thereafter (every 'even' week). Can the formula be amended so that expenses can fall in 'odd' weeks. I think the best way to do this would be to amend the 'fortnightly' formula so that it also references any date entered in R14 - not only would this give a clear instruction to the formula which fortnightly cycle the expense would be incurred in, but would also prevent the cashflow from back-populatin e.g. if a fortnightly expense only begins in 01 May 2016 as per R14, nothing would be generated for the months preceeding this date

- If I select the 'fortnightly' option and change the date from 'Thursday' to any other day, the formula doesn't work

- I've integrated your single formulas into the longer one above and with the exception of the fortnightly formula which slots in perfectly (although only works when 'Thursday' is selected), none of the other formulas work and each time I incorporate the bi-monthly or quarterly or bi-annual formula, the formula returns a value error. Please see the attached

From which date do you want the time periods to be measured: from R14 or K14.

Bimonthly works fine (I changed the "if nothing" condition from zero to blank (enabling count to work) and copied the formula along the row (it was only present in Y14). Quarterly and bi-annually may well work, or not work, but you have not incorporated them into your formula. You fix that... and I will look at fortnightly.

Hi Glenn,

R14 should be the reference. K14 and L14 exists only for those expenses which we have intermittently e.g. if we have a project at a particular satellite location for 3 months, starting February and ending April, we may decide to pay employees a daily travel allowance to get them to and from site. In this case I will set the payment frequency as daily, but K14 will be set to 01 February 2016 and L14 will be set to 30 April 2016. The formula at the very beginning of each cell in columns Y - NZ =IF(OR(Y$13<$K14,Y$13>$L14),"", will ensure that the daily cost will only appear in the period between those dates in K14 and L14 as oppose the entire year.

I think the 'Day of Week' option in column N should only be reserved for when the payment frequency is set as 'weekly'. For fortnightly payment frequencies, I don't think column N is relevant, instead I will just enter a date in R14 and then the payments will appear in that date and every date that is a multiple of 14 days from that date.

Attached is the amended sheet with quarterly and bi-annually included. I've just got a load of errors showing in most cells but oddly enough, the correct amounts are still showing where they should be.

Ok. It's beer o'clock here. I'll have another quick look at this, but it may be tomorrow before I get back to you.

In the meantime, reads this and apply it to a duplicate copy of your overall sheet. You file is suffering from Excel bloat in the Master sheet.

The first step in eliminating superfluous formatting is to figure out where your worksheet's data ends-the bottom righthand corner of your data, if you will. Don't rely on Edit » Go To... » Special » Last Cell, as this might take you to the last cell containing formatting, not actual data. Having manually located the cell you know to be your last cell containing legitimate data, highlight the row immediately following it. While pressing the Ctrl and Shift keys, press the down arrow on your keyboard to highlight all rows beneath that row and select Edit » Clear » All to clear them.

Now apply the same logic to unwanted formatting lurking in your columns. Locate the cell in the last column containing data and click the column header of the column immediately to the right. Press Ctrl-Shift and the right arrow on your keyboard to highlight all other columns to the right and then select Edit » Clear » All.

Don't be tempted to actually delete these rows or columns rather than clearing them, as doing so often causes the dreaded #REF! error in any cells of any formulas that might reference them.

Save your workbook and take gleeful note of the change in its file size by selecting File » Properties... » General.

OK. It started to rain here; so I finished this (I hope).

I re-worked the way the date is assigned (cells N15 to R15. Just enter the date at R15).

it seems OK now,but note (again) that the END of the formula now reads:,$R14=Y$13),$V14,"")))))))))) and not ,$R14=Y$13),$V14,0))))))))))

Any problems shout. Now it really is beer o'clock. G'nite,