Formula to calculate after a set amount of days

[whatdoido]

Afternoon!

I've been tearing my hair out over this, and I hoping for some enlightenment from everyone here!

I would like a formula that will calculate the number of days after a certain date.

Basically we offer a storage service, which has an incentive of a number of days (different for each customer) free storage per item, the number of days free is presented in a cell for the formula to reference (so it can be changed easily later down the line). We bill monthly, so I was hoping for a formula that will take into consideration the free period, but will not keep deducting those seven days each billing period?

I hope I have explained that enough to make some sense

I have attached a screen shot below to help.

Column 'K' is where the formula will be placed (the formula there doesn't work, so those values are incorrect)
Cell 'I4' is the free period
Column 'I' is the date of the last bill sent to client or if after that date the date the item arrived in depot

I added column 'R' as a work around, which I don't know if it will help or not, it is a basic formula "=IF(B9<>"",B9+$I$4,"")"


Any advice or criticism is welcome!!
Thank you

 

[awoohaw]

what do you want the formula in column K to calculate? What number of days after what date?

 

[whatdoido]

what do you want the formula in column K to calculate? What number of days after what date?

Sorry!

The difference between the dates in columns I + J, but only after the free period.

 

[awoohaw]

Sorry!

The difference between the dates in columns I + J, but only after the free period.

not sure what you mean? dates are "date" and a free period is not a date.

Examples help.

do you mean if the "END of the free period" is in the billing cycle to use that as the days calcuation? So, if free period is 7 days, and it ended on 5/1 and the billing (assuming monthly billing) day is the 15 that you only bill from 5/2 to 5/15 instead of 4/16 to 5/15.?

 

[whatdoido]

not sure what you mean? dates are "date" and a free period is not a date.

Examples help.

do you mean if the "END of the free period" is in the billing cycle to use that as the days calcuation? So, if free period is 7 days, and it ended on 5/1 and the billing (assuming monthly billing) day is the 15 that you only bill from 5/2 to 5/15 instead of 4/16 to 5/15.?

I am sorry that I have not been clear enough!

What you have said is what I was thinking.
Would this then, going forward, use the difference between the the "last bill date" (I) and the "End date" (J) to calculate the no. of days, until the item left our depot?

Thank you for deciphering my madness!

 

[awoohaw]

editing

 

[awoohaw]

I'm not sure if this is what you want or not. I am just giving relevant calculations here. I hope you can figure out what to do with them.
And, I'm not sure I have understood completely. But, as I said examples would have helped.

Book1
	A	B	C	D	E
1			Current Date:	2024-05-10
2
3
4	In/Start Date	Free Days	Billing Day	Billing Date	Days To Bill
5	2024-02-15	8	15	2024-05-15	30
6	2024-04-25	8	25	2024-05-25	22
7	2024-04-05	8	5	2024-05-05	22
Sheet1

Cell Formulas
Range	Formula
C5:C7	C5	=DAY(A5)
D5:D7	D5	=DATE(YEAR($D$1),MONTH($D$1),DAY($A5))
E5:E7	E5	=MIN($D5-($A5+$B5),$D5-EDATE($D5,-1))

 

[whatdoido]

Thank you!

The formula in E5 seems to work, however if the date difference is less than the free days it produces a minus value.
Could an if function be added to produce a '0' if number of days to bill is less than the free period?

Many thanks

 

[whatdoido]

Thank you!

The formula in E5 seems to work, however if the date difference is less than the free days it produces a minus value.
Could an if function be added to produce a '0' if number of days to bill is less than the free period?

Many thanks

Sorry, just noticed if there are no values in the DATE IN or the END DATE it throws an error, are we able to make it stay blank until there are values in all the columns referenced?

 

[whatdoido]

I've added some values to this for example

Once again thank you for your help!