Need your help to create a timeline column

[MOhammedaldb]

Hello this thread is not active because the data in the files I posted were not accurate and caused a lot of trouble please go to the new thread were I corrected the data in this link thank you.

https://techcommunity.microsoft.com/t5/excel/need-your-help-to-create-a-time-in-days-formula/m-p/377... 

[#2]

@MOhammedaldb 

w/modified column used in the math:

=MAP(Table1[Close Date],Table1[Created on],Table1[Modified On],LAMBDA(h,i,j,IF(ISBLANK(h),j-i,h-i)

w/TODAY() function used in the math (This changes the results considerably):

=MAP(Table1[Close Date],Table1[Created on],LAMBDA(h,i,IF(ISBLANK(h),TODAY()-i,h-i)))

If this is what you're looking for and want to put the 'timeline' in the table, then we'd go with a logical IF and forget MAP.  Outside of the table, MAP's results are dynamic based on the table.

[#3]

@MOhammedaldb 

Do you really want to calculate CreatedOn minus today? You get a of lot negative values.

And there is something wrong with your dates. Some of them are in the future.

[#4]

Hello Detlef, regarding the " CreatedOn minus today" yes my mistake i switched them around i meant " Today - Created On "
and regarding the future dates some of them is " Estimated Close date > Est Close Date " it's just a estimate on when the deal will be closed, and regarding the closed deals that are in the future yes indeed your right but this is just an example on how the data is structured if you are saying that the formula wont work because of this i will update the dataset now thank you

[#5]

@MOhammedaldb 

Then it is simply:

=MAX([@[Close Date]],TODAY())-[@[Created on]]

Future dates will have negative values.

[#6]

wow thank you so much Patrick i tried the second formula and it worked perfectly, but just a question how will the formula differ if we use IF instead of MAP and yes I'm intending to use your formula inside the same table.

[#7]

If the formula is going in the table, then go with IF. The results of MAP spill and tables are scalar-based (1 cell, 1 formula, 1 return).

[#8]

yes when i try to do it in my dataset it gives me SPILL error could you please show me how i can do it with IF