Formula for calculating between two dates in columns


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


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
:magic_wand: Column Magic :sparkles:- the magical columns toolbox app

Absolute magic. Thank you. Works a charm :+1:t3:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.