yogi_Compute Days Elapsed Between Signing Of Contract And Implementation Date ...

Google Spreadsheet   Post  #2191

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-23-2017

question by erijohns:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/7F67kIklQWw;context-place=mydiscussions
If date is before another...

Hi!

I've been reading through the Forum for the past couple days and learned a ton, but I haven't a seen a use case like this one and I'm stuck: 

My company has a product that can be implemented one of two ways. I have a spreadsheet with three columns: the Contract Signed Date, Go Live Date (Option A), and Go Live Date (Option B). 

Some clients have fully implemented our product, some have just implemented on one or the other, and some have only signed a contract -- they're being implemented but aren't live yet. 

I want to calculate how long, in days, it takes a given client to go live for the first time with our product, and if they aren't live yet, how long it's been since they signed the contract. I can figure out how to calculate how long it's been since Signed Contract, and I can figure out how to calculate DATEDIF between Signed Contract and one of the Go Live Dates, but if a client has fully implemented with us, then they have values in all three columns: they signed a contract, went live with Option A, and went live with Option B. 

So the trouble I have is: how do I create a formula that, if it sees two go-live dates (values in columns E, F and G), picks the one closest to the Signed Contract date and calculates how long it took to go live that first time? 

Here are the five scenarios I could think up: 

1. F=Blank, G=Blank (in which case, calculate E to Today)

2. F<>Blank, G=Blank (in which case, calculate E to F)

3. F=Blank, G<>Blank (in which case, calculate E to G)

4. F<>Blank, G<>Blank, F>G (in which case, calculate E to F, I think)

5. F<>Blank, G<>Blank, F<G (in which case, calculate E to G, I think)

It's possible that I'm not interpreting how a date could be less than or greater than another date. That's a bit confusing. 

Oh and here's some example data: 

1. E                   F                     G

  1/1/17  

The correct answer would be: 171 (when I posted this topic).

2. E                   F                     G

 1/1/17         1/2/17

The correct answer would be: 1

3. E                   F                     G

 1/1/17                                1/2/17

The correct answer would be: 1

4.  E                   F                     G

 1/1/17          1/5/17           1/4/17

The correct answer would be: 3

5.  E                   F                     G

 1/1/17         1/10/17          1/20/17

The correct answer would be: 9

Hopefully this makes sense. Here's my best attempt at a formula so far. It's so close, but I'm not expressing myself well enough. 

=IF(AND(F8<>G8<>“”,F8<G8),DATEDIF(E8,F8,“D”),IF(AND(F8<>G8<>“”,F8>G8),DATEDIF(E8,G8,“D”),IF(AND(ISBLANK(F8),ISBLANK(G8)),DATEDIF(E8,TODAY(),“D”),IF(AND(ISBLANK(F8),NOT(ISBLANK(G8))),DATEDIF(E8,G8,“D”),DATEDIF(E8,F8,“D”)))))

Thanks so much