Mar 26 2023 01:26 PM
Mar 26 2023 01:26 PM
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
And here is the link for the Excel file in google drive.
Thank you so much in advance I hope you can help me
Mar 26 2023 02:05 PMSolution
=IF([@[Close Date]]="",TODAY(),[@[Close Date]])-[@[Created On]]
Format the column with this formula as General or as Number.
Mar 26 2023 02:19 PM
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?
Mar 26 2023 04:36 PM
Mar 26 2023 10:33 PM
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.
Mar 27 2023 11:26 AM
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,
Mar 27 2023 12:28 PM
I'm sorry. My formula should have used MIN() instead of MAX().
=MIN([@[Close Date]],TODAY())-[@[Created on]]