Finding first date prior to another which is a multiple of a certain number of months.

[SYKEMAKAVELI]

Hi,

I have the following fields / inputs:
1) 'Start date' - in this example 9 June 2023, cell AV11
2) 'First date' - in this example 1 July 2013, cell AU16
3) 'Frequency' - 365.25 (in days), cell AV14 --> this can take any numeric value, and would typically range from 1 to 365.25

I have created a formula which finds the first date >= the 'First date', which is an exact multiple of 'Frequency' days prior to the 'Start date':
IF(AU16="","",IFERROR(AV$11-AV$14*FLOOR((AV$11-AU16)/AV$14,1),""))
This gives an output of 8 July 2013, the first date from 1 July 2013 which is an exact multiple of 365.25 days prior to 9 June 2023.

However, I want to modify the formula so that it retains the day value from the 'Start date'. i.e. instead of using 365.25 days as an input, I want to modify the formula to use a new input
'Mths offset' (cell AV13), which will be an integer value of 1, 2, 3,..., 12. The idea is effectively the same as above - I want to find the first date that is on or post 1 July 2013 (in this example), which is a multiple of 'Mths offset' months prior to the 'Start date'.
For example, if I used a value ' Mths offset' of '12', I would want it to return 9 July 2013, preserving the day value from the 'Start date' field.

Does anyone have a view as to how I can go about doing this, preferably (strongly) within a single formula?

Thanks in advance

 

[awoohaw]

would you be kind and post your data using the xl2bb add in (link below). Please included some expected results.
You can sanitize/dummy up your data for security.

Thanks in advance, and best wishes.

 

[SYKEMAKAVELI]

Thanks for getting back to me.

I've pasted an example of what I"m after in the minisheets file below. I've shifted the cell references over, so they won't align to what I've outlined above, but hopefully you can follow from the formula in the sheet.

The target output is in the two right-hand columns.
Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		Inputs
3
4		Current inputs
5		Start date	9/07/2023
6		Frequency	365.3	days
7
8		Additional inputs to use (see explanation in columns F, onwards)
9		Mths offset	12	This input can be any integer from 1 through to 12
10
11		Calcs / Output
12
13				Current Output		Target Output
14		First_date	End_date	1st trans.	Last trans.	1st trans.	Last trans.
15		1/07/2013	29/05/2023	8/07/2013	8/07/2022	9/07/2013	9/07/2022
16		30/05/2023	30/12/2023	9/07/2023	9/07/2023	9/07/2023	9/07/2023
17
18				Current output - showing the 1st and last transaction in the date range, based on the required frequency.		Desired output -
19
20						I want to use cell C9 (Mths offset) to give the 1st and Last transaction date again, but using whole months and preserving the days in the "Start date" field.
21						When I use days to deduct (like in coluns D / E), it will stuff up if I deduct, or add, past a leap year. Hence the desire to use whole months instead and keep the days value.
22						The case where 'Mths offset' = 12 is probably an easy one to 'hack', but I want the formula to be scalable to any months deduction value.
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
C15	C15	=+B16-1
D15:D16	D15	=IF(B15="","",IFERROR(C$5-C$6*FLOOR((C$5-B15)/C$6,1),""))
E15:E16	E15	=IF(D15="","",IFERROR(D15+FLOOR((C15-D15)/C$6,1)*C$6,""))