Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that counts the number of days between 2 dates, but it is dependent on if there is a date in a certain column. So if there is a 'won' date it shows this, if there is a 'lost' date (different column) to show this and if there is no date in either column to show against the pull date stored in a single cell
the start date is in column AV
Won date column is BP
Lost date Column is BC
Pull date is cell BW6
Solved by A. A. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/08/2018 - 08:34
Hello
Excelchat Expert
08/08/2018 - 08:34
Welcome
User
08/08/2018 - 08:34
Hey
Excelchat Expert
08/08/2018 - 08:35
Great problem description.
Excelchat Expert
08/08/2018 - 08:35
On addition if you can show me example in the preview window it would be really helpful to understand the problem clearly.
Excelchat Expert
08/08/2018 - 08:36
Can you do that please?
User
08/08/2018 - 08:36
sure I'll drop in the relevant columns
Excelchat Expert
08/08/2018 - 08:36
Thanks.
User
08/08/2018 - 08:38
sorry hang on, had accidentally left a filter on
Excelchat Expert
08/08/2018 - 08:38
Not a problem, let me know when you are finished.
User
08/08/2018 - 08:39
ready
Excelchat Expert
08/08/2018 - 08:40
Now, what you need here is?
User
08/08/2018 - 08:41
in column D that it calculates the days between the creation date and either the won or lost date, if niether is present, the days between the creation date and the pull date
User
08/08/2018 - 08:41
to see how long the deal has been active
Excelchat Expert
08/08/2018 - 08:42
Okay. If both won/lost date exists then?
User
08/08/2018 - 08:42
it can't, it is either won or lost
Excelchat Expert
08/08/2018 - 08:43
Thank you for making it clear. Please give me some time.
User
08/08/2018 - 08:43
thanks
User
08/08/2018 - 08:47
thats amazing thank you!
Excelchat Expert
08/08/2018 - 08:48
=IFERROR(IFERROR(DATEDIF(A2,B2,"D"),DATEDIF(A2,C2,"D")),DATEDIF(A2,$G$1,"D"))
Excelchat Expert
08/08/2018 - 08:48
Here is the formula but there is a catch.
Excelchat Expert
08/08/2018 - 08:48
IF deal creation date is greater than either Won/Lost/Pull date then you will get error.
User
08/08/2018 - 08:49
okay thats understandable, and il pulled from a system rather than manually input so shouldnt happen
User
08/08/2018 - 08:49
nut thank you so much
Excelchat Expert
08/08/2018 - 08:49
Great.
User
08/08/2018 - 08:49
but*
Excelchat Expert
08/08/2018 - 08:49
I'm happy to be able to help.
Excelchat Expert
08/08/2018 - 08:49
Do you need an explanation of the formula?
User
08/08/2018 - 08:49
no thats fine
User
08/08/2018 - 08:50
thanks for all your help
Excelchat Expert
08/08/2018 - 08:50
Again great to hear that. Please rate me 5 star if you think you were benefited from my service.
Excelchat Expert
08/08/2018 - 08:50
Have a great day!
User
08/08/2018 - 08:50
of course, how do I do that?
Excelchat Expert
08/08/2018 - 08:51
You can click on the END button to close the chat and leave feedback.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
