Splitting a date range into percentages per month.

[GermanH]

Hello all, 

I'm trying to split dates and apply a percentage completed per month based on how much of that date range lies in each month. Does anyone have an example of how they achieved something similar?

I tried my hand at a formula but its giving me a malformed function error.

[binu_acs]

@GermanH Your date fields are not in actual date format, first you need to convert them into date format (yyyy-mm-dd) then apply your formula, to convert the given dates to ISO format either you can use the DateTimeParse() function or DateTime Tool

[PangHC · Accepted solution]

the formula have error. in the min() part. it missing ")" that cause the syntax error.
and in the min(), it should be "2023-12-01" instead of "2023-11-01"

to do as dynamic month (avoid hundreds of formula), use generate row to generate month and change abit in the formula. (so 1 formula is applied to any month), and crosstab later to see the months, and you can rename if required.

however, it different from what I calculate.

[p2] is follow your formula:


here my formula