Need your help to create a time in days formula

[MOhammedaldb]

Hello guys how are you doing I hope you are doing well,

This might seem like a similar discussion that I already made, and yes you are right but the difference is that I updated the data that I used and made it more accurate and more realistic and I have an example of how I want to make the formula, so before I start let me show you how the data is structured,

as you can see what I want to do is to have a "Time In Days" column which basically tells me the time in days from the moment we took the deal to the moment we closed the deal whether it's " Won " or " Lost " and in this case it's easy I take the " Close Date " minus the " Created On" and it will give me time in days on how long did we take to close the deal, but the problem is there are some deals that are open status which means that they are not closed yet like " Early Stage " and " Under Evaluation " and " To Be Qualified " and they don't have a " Close Date " because they are not closed yet, in my previous discussion I learned about the TODAY() function which gives me the date today in real-time and this is perfect for open deals to know how many days this deal is taking the thing I need you help on is how can I write a formula that says if the " Close Date " contains data which means it's a closed deal then take the " Close Date " minus " Created On " but if the " Close Date " is blank do this instead TODAY() minus " Created On ".

If my explanation is not clear here is a 2 min video in google drive explaining what I mean better

https://drive.google.com/file/d/1dVDzVowao1ylMxcGPWo2LwMQqpdCzoFU/view?usp=sharing 

And here is the link for the Excel file in google drive.

https://docs.google.com/spreadsheets/d/1HLrcIAox_ipGRSbPilR4QiubUY_2VaAP/edit?usp=sharing&ouid=10930... 

Thank you so much in advance I hope you can help me 

Windows 10 

[#2 · Accepted solution]

@MOhammedaldb 

Use

=IF([@[Close Date]]="",TODAY(),[@[Close Date]])-[@[Created On]]

Format the column with this formula as General or as Number.

[#3]

@MOhammedaldb 

---

@MOhammedaldb wrote:

This might seem like a similar discussion that I already made, and yes you are right but the difference is that I updated the data that I used and made it more accurate and more realistic  

---

So, you are telling us that the solutions for the previous request don't work with the more accurate data?

https://techcommunity.microsoft.com/t5/excel/need-your-help-to-create-a-timeline-column/m-p/3778751

[#4]

Thank you so much Hans it works perfectly

[#5]

Hello Detlef, I don't know what seems to be the problem with that code but the thing that I noticed that all the numbers are perfectly sorted like it starts from 606 for example and then the next cell is either 606 or 605 i don't know much about Excel code i just started 2 months ago learning Excel so i cant give you the reason why the code is showing up like this, but thank you for looking in to my problem again

[#6]

@MOhammedaldb 

You should have continued on the old thread and not started a new one.

Helpers who don't know about the old thread also would not know about the other proposed solutions. And helpers who contributed to the old thread could be embarrassed or angry because you did not bother or were not willing to notify them that there solutions may be wrong.

However, I did not check your new data but the solution provided by @Hans Vogelaar should give the same results as my formula.

[#7]

@Detlef Lewin 

The problem with the old thread is that some of the data don't make sense as you said there were deals that are closed in the future and this will only make you and other helpers confused so I thought instead of going back and forth with data that is flawed and not accurate I tried to delete the old thread but it seems that I couldn't so rather than explaining it again in one thread I thought that making a new thread is the best solution my intentions were not to make you and the other helpers angry so sorry about that,

regarding the formula, as you can see in the pictures your formula seems to be doing something wrong as you can see any deal with open status ( no close date ) is right and gives me the same time in days as Hans's code, the thing is I think your code is taking the TODAY() function wither the deal have a close day or wither it doesn't as you can see in the photos below.

with close date 

no close date 

and sorry again if I didn't do the right thing regarding the last thread this is my first time here and I'm still learning,

thank you.

[#8]

@MOhammedaldb 

I'm sorry. My formula should have used MIN() instead of MAX().

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