IF AND formula

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
267
Office Version
  1. 2016
Platform
  1. Windows
Hi I've been struggling to write a formula for my cash flow forecast for a week:(

I have:
1. list of potential staff
2. their potential employment start date, highly subject to change
3. their cost of full time employment over a 36 month period...based on them all starting on the same date which is the problem here as there will be many different dates
4. their cost of recruitment/mobilisation which will be applicable 14 days before they start work

For each person their start date is column F62 and is in format dd-mm-yy.

Columns AV62 to CE62 (36 columns) is the cost of employment for each full month... assuming they work a full month..

Column AU62 is the mobilisation cost for each staff member which has to be paid 14 days before they start.

The cash flow forecast needs to run from April 2019 as first employees expected to start early May.... i.e. if they start first half May then their mobilisation cost will go into April 2019.

So I'm trying to write a formula which I can drag out over 37 columns (April 19 + a further 36 months) which will
1. look at the employee start date F62, F63, F64 etc.
2. compares it to the current month of the forecast (which starts in April 19), I've currently got April-19 just typed into cell CH60 then June 19 CI60 etc
3. then look at column AU62 to CE62 with the associated costs
4. enter these costs into the correct month based on the start date

rules/examples
1. mobilisation cost AU62 needs to go in the month 14 days prior to start date which of course might still be the same month
2. if a person starts on 10th May then their mobilisation cost (cell AU62) will go in April and their "month 1" May cost (cell AV62) will be on pro-rata basis for the days employed in that month.. e.g. 31 less 9.
3. if a person starts on 14th June then their mobilisation cost will go in May and their "month 1" June cost will be 30 less 13.
4. if a person starts on 20th November then their mobilisation cost will be in November and their "month 1" November cost will be pro-rata for the days employed in November which would be 11 (20th Nov to 30th Nov).

Hopefully there's someone who can understand and suggest a formula for all this
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
check it carefully


Book1
FAUAVAWCDCECFCHCICJCKCQCR
60Apr-19May-19Feb-22Mar-22Apr-19May-19Jun-19Jul-19Jan-20Feb-20
61
6208/05/195001000100010001000250.01024.21000.01000.01000.01000.0
6309/06/19100020002000200020000.0428.62038.12000.02000.02000.0
6404/07/19150030003000300030000.00.01178.63031.13000.03000.0
6518/07/19200040004000400040000.00.00.03806.54000.04000.0
6601/08/19250050005000500050000.00.00.02500.05000.05000.0
Sheet7
Cell Formulas
RangeFormula
CH62=$AU62/14*IF(IF($F62-14>=CH$60,$F62-14),MIN(14,DAY(EOMONTH(CH$60,0))-DAY($F62-14)+1), IF(IF($F62-14>=CG$60,$F62-14),14-(MIN(14,DAY(EOMONTH(CG$60,0))-DAY($F62-14)+1)),0))+ AV62/DAY(EOMONTH(CH$60,0))*IF(AND($F62<=EOMONTH(CH$60,0)), IF(AND($F62<=EOMONTH(CG$60,0))=FALSE,DAY(EOMONTH(CH$60,0))-DAY($F62)+1, DAY(EOMONTH(CH$60,0))),0)
 
Last edited:
Upvote 0
Hi Alan sorry I can't get it to work the way I envisioned

I think better if I label AU60, AV60, AW60, AX60 etc. as Month 0, Month 1, Month 2, Month 3 etc. even though they are not linked to anything

If I enter:
AU62 = 1000 (this would be month 0, the mobilisation cost due 14 days before start date)
AV62 = 100 (this would be month 1 which might be pro-rata for the first month dependent on what date in the month the person starts)
AW62 = 200 (this amount would be payable month 2 regardless of when the person starts in month 1)
AX62 = 300 (month 3)
AY62 = 400 (month 4)
AZ62 =500 (month 5)
BA62 = 600 (month 6)

and use start date 01 June 2018 in F62... this would be the employee's "month 1" regardless of whether they worked 1 day or 31 days

then
CH62 (April 19, cash flow month 0) should be zero... this works
CI62 (May 19, cash flow month 1) should be 1,000.... this works
CJ62 (June 19, cash flow month 2) should be 100 as it's the month the person started work and they worked the full month ... but it's returning 300 as it's looking at AX62 (month 3)

I'm not sure if it's a simple fix or if I haven't explained the problem very well

if I change start date F62 to 21-06-19 then:
CH62 (April19) should = zero... this works
CI62 (May 19) should = zero... this works
CJ (June 19) should = the "mobilisation" cost from AU62 plus 10 days (21st June to 30th June) worth of the "month 1" cost from AV62
CK (July 19) should = the "month 2" cost from AW62
CL (Aug 19) should = the "month 3" cost from AX62

I guess the whole thing would be a lot easier if I made everyone start on the first day of each month and called this the month 1 and the prior month as the full mobilisation cost...
 
Upvote 0
right, this might do it


Book1
FAUAVAWAXAYAZBACGCHCICJCKCLCMCN
60Apr-19May-19Jun-19Jul-19Aug-19Sep-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19
61
6201/06/1910001002003004005006000.01000.0100.0200.0300.0400.0500.0
6308/06/1910001002003004005006000.0500.0576.7200.0300.0400.0500.0
6416/06/1910001002003004005006000.00.01050.0200.0300.0400.0500.0
6501/07/1910001002003004005006000.00.01000.0100.0200.0300.0400.0
6608/07/1910001002003004005006000.00.0500.0577.4200.0300.0400.0
6716/07/1910001002003004005006000.00.00.01051.6200.0300.0400.0
Sheet7
Cell Formulas
RangeFormula
CH62=$AU62/14*(IF(AND($F62-14>=CH$60,$F62-14<=EOMONTH(CH$60,0),DAY($F62)<=14), EOMONTH(CH$60,0)-($F62-14)+1,0)+IF(AND(EOMONTH(CH$60,0),$F62>=CH$60, $F62<=EOMONTH(CH$60,0)),IF(DAY($F62)<=14,DAY($F62)-1,14),0)) +IF($F62$AU62,,DATEDIF($F62,EOMONTH(CH$60,1),"M")) *MIN(1,(EOMONTH(CH$60,0)-$F62+1)/DAY(EOMONTH(CH$60,0))),0)
 
Upvote 0
Superb that appears to do the trick! Only tried a couple of examples but works great! Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top