Multifield formula Complexity

[AS]

Hi Experts,

Stuck in a complex situation where I have to use same formula in multiple field with some changes in the value. Please help me to solve this.

The first table is containing the data from 2018 to 2022 in below format -

Max Date	Min Date	Diff Column	Month
31-01-2018	01-01-2018	31	Jan_2018
28-02-2018	01-02-2018	28	Feb_2018
31-03-2018	01-03-2018	31	Mar_2018
30-04-2018	01-04-2018	30	Apr_2018
31-05-2018	01-05-2018	31	May_2018
30-06-2018	01-06-2018	30	Jun_2018
31-07-2018	01-07-2018	31	Jul_2018
31-08-2018	01-08-2018	31	Aug_2018

second table has some data- including formula tool

Allocation Started Month	Allocation End Month	31-01-2018	28-02-2018	31-03-2018	30-04-2018	31-05-2018	30-06-2018	31-07-2018	30-08-2018
02-01-2018	20-04-2018	44082.8	42082.8	44082.8	44082.8	0	0	0	0
14-05-2018	13-08-2024	0	0	0	0	0	256865	256865	256865
14-08-2019	13-08-2024	0	0	0	0	0	0	0	0

Single formula will applied for all column but the difference is [Max Date], [Min Date] and [Diff Column] will change according to the first table. 

formula for 31-01-2018 field --- IF [Allocation start month]>[Max Date] then 0, elseif [Allocation end month]<[Min Date] then 0 elseif [Allocation start month]>=[Min Date] and [Allocation start month]<=[Max Date] then [TCV] else [TCV]*[Diff Column]

Please help me in solving this complex logic

[AS]

@atcodedog05 Could you please help this time?

[atcodedog05]

Hi @AS 

It is not very clear. What is TSV and can you provide us with an expected output?

[AS]

Hi @atcodedog05 

To answer your question [TCV] would be the another column where I'm copying the Amount or doing calculation. But my question is divided into 2 part

1. How 1st table data rows can be bring in the formula (as you can see 2nd table column names are same as rows of first table)

2. how I can implement same formula in every field

The first table is containing the data from 2018 to 2022 in below format -

Max Date	Min Date	Diff Column	Month
31-01-2018	01-01-2018	31	Jan_2018
28-02-2018	01-02-2018	28	Feb_2018
31-03-2018	01-03-2018	31	Mar_2018
30-04-2018	01-04-2018	30	Apr_2018
31-05-2018	01-05-2018	31	May_2018
30-06-2018	01-06-2018	30	Jun_2018
31-07-2018	01-07-2018	31	Jul_2018
31-08-2018	01-08-2018	31	Aug_2018
and so on

TCV	Allocation Started Month	Allocation End Month	31-01-2018	28-02-2018	31-03-2018	30-04-2018	31-05-2018	30-06-2018	31-07-2018	30-08-2018
44082	02-01-2018	20-04-2018	44082.8	44082.8	44082.8	44082.8	0	0	0	0
256865	14-05-2018	13-08-2024	0	0	0	0	0	256865	256865	256865
34342	14-08-2019	13-08-2024	0	0	0	0	0	0	0	0

formula for 31-01-2018 field --- IF [Allocation start month]>[Max Date] then 0, elseif [Allocation end month]<[Min Date] then 0 elseif [Allocation start month]>=[Min Date] and [Allocation start month]<=[Max Date] then [TCV] else [TCV]*[Diff Column]

[atcodedog05 · Accepted solution]

Hi @AS 

It would be something like this. Please check the formula to get accurate values

Hope this helps : )

[AS]

@atcodedog05 

Thank you so much. You are a true savior.

[AS]

Thanks

[atcodedog05]

Happy to help : ) @AS 

Cheers and have a nice day!