Formula for calculating between two dates in columns

[MaxLogic_Lisa (Lisa Armstrong)]

Hello

I have 4 columns:
Start, End, Term and Invoice £

Example data:
1 Apr, 2021, 30 Apr, 2022, 12, £15,000

I then have additional formula columns
Jan-2021, Feb-2021, Mar-2021 etc to Dec-2021

I need a formula for example to put in Apr-2021 column

If April 2021 falls between the Start and End column then divide the Invoice £ by the Term if not just put zero.

This would then be copied to the rest of the columns but with a change if the If April 2021.

Hope that makes sense.
Many thanks
Lisa

[JCorrell (Jim - The Monday Man)]

@MaxLogic_Lisa

Try this:
IF(AND(DAYS("2022-4-1", FORMAT_DATE({Start})) >= 0, DAYS(FORMAT_DATE({End}), "2022-4-1") >= 0), {Invoice £} / {Term}, 0)

---

Jim - The Monday Man
Column Magic - the magical columns toolbox app

[MaxLogic_Lisa (Lisa Armstrong)]

Absolute magic. Thank you. Works a charm