IF AND formula

[BrutalLogiC]

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

 

[BrutalLogiC]

'elp!

 

[AlanY]

check it carefully

Book1
	F	AU	AV	AW	CD	CE	CF	CH	CI	CJ	CK	CQ	CR
60			Apr-19	May-19	Feb-22	Mar-22		Apr-19	May-19	Jun-19	Jul-19	Jan-20	Feb-20
61
62	08/05/19	500	1000	1000	1000	1000		250.0	1024.2	1000.0	1000.0	1000.0	1000.0
63	09/06/19	1000	2000	2000	2000	2000		0.0	428.6	2038.1	2000.0	2000.0	2000.0
64	04/07/19	1500	3000	3000	3000	3000		0.0	0.0	1178.6	3031.1	3000.0	3000.0
65	18/07/19	2000	4000	4000	4000	4000		0.0	0.0	0.0	3806.5	4000.0	4000.0
66	01/08/19	2500	5000	5000	5000	5000		0.0	0.0	0.0	2500.0	5000.0	5000.0
Sheet7

Cell Formulas
Range	Formula
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)

 

[BrutalLogiC]

Thanks so much AlanY, I am going to try this tomorrow morning

 

[BrutalLogiC]

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...

 

[AlanY]

right, this might do it

Book1
	F	AU	AV	AW	AX	AY	AZ	BA	CG	CH	CI	CJ	CK	CL	CM	CN
60			Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19		Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19
61
62	01/06/19	1000	100	200	300	400	500	600		0.0	1000.0	100.0	200.0	300.0	400.0	500.0
63	08/06/19	1000	100	200	300	400	500	600		0.0	500.0	576.7	200.0	300.0	400.0	500.0
64	16/06/19	1000	100	200	300	400	500	600		0.0	0.0	1050.0	200.0	300.0	400.0	500.0
65	01/07/19	1000	100	200	300	400	500	600		0.0	0.0	1000.0	100.0	200.0	300.0	400.0
66	08/07/19	1000	100	200	300	400	500	600		0.0	0.0	500.0	577.4	200.0	300.0	400.0
67	16/07/19	1000	100	200	300	400	500	600		0.0	0.0	0.0	1051.6	200.0	300.0	400.0
Sheet7

Cell Formulas
Range	Formula
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)

 

[BrutalLogiC]

Superb that appears to do the trick! Only tried a couple of examples but works great! Thanks!

 

[AlanY]

you're welcome