calculating # of days from install to remove by address

I'm a newbie to this group thanks for the awesome knowledgebase!

My question is what is preferred conditional formula to determine number of days from "add" to "remove" (in SERVICE TYPE column) based on ORDER # (ORDER # is same as SERVICE ADDRESS fyi).

Would you recommend using = DATEDIF and if so, how would the conditional part of the formula be written? I am struggling to figure out an elegant way (& scale-able!) to do this

sample data.jpg

C22

=if(countif($B$22:$B22,$B22)=1,"add","remove")

and drag down.

If your data is always in this order, then you can use this:

Please Login or Register  to view this content.

thank you oeldere

I am confused though as your formula output gave answer of "add" and "remove" when formula was dragged downward ...I am seeking to determine the # of days between SERVICE TYPE where the ORDER # is exactly the same. I am thoroughly STUMPED on how to write the conditional aspects of a workable formula on this one (I have a bad case of newbie-itis!)

Then you are Lucky,

you get the formula for column C in #2

you get the formula for column E in #3 (nigelbloomy)

This will find the difference no matter where the customers are located.

Please Login or Register  to view this content.

and no sir (@nigelbloomy), the data is always in a different order but the ORDER# is always the same per property address. See below for different data set:

sample data2.jpg

I am assuming the answer is a formula that uses a combination =DATEDIF and IF formulas Or is there a better easier way?

If you always have the add line for a customer directly above the remove line for that same customer (like you have above), then my first formula will work to find the difference in days.

If you have times where the add and remove lines are not one on top of the other, then my second formula will find the difference.

@denverdriveby

It is always good to show the real data (from the beginning) , so forummembers can give the correct answers.

The answer in #3 fits your posted needs.

Option: you could sort the data.

@nigelbloomy & @oeldere : thank you both, your insight & formula solved my question. I am studying the syntax now to better understand the math & conditional statements and also to scale-up the formula to my entire dataset. Thank you both sincerely, I hope I can repay the favor someday. Solution added to dataset and shown below:

sample date 3.jpg

dutch

Glad it's working for you.

Glad I could help. If the question is solved, will you mark it solved?

You can add (rep)utationpoints to the one who helped you, by clicking on the star on the left side.

for other people's benefit: I have posted the correct formula into the spreadsheet and show below how it appears. The output data shows whole days listed with 2 decimal places. Thank you again each of you for your patience and expertise. Much obliged!

sample date 3.jpg

I get the follow faillure, when I try to open the attachement 398790

Invalid Attachment specified. If you followed a valid link, please notify the administrator.

There's a problem with the attachment. Please retry.
To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

well shoot, I don't know why the images show at the top but not below perhaps it's because I'm a new user and don't have certain permissions?? I am happy to email either of you a screenshot showing how your solutions answered my question. <email removed per forum user's advice >

I advice you to remove the e-mail adres.

It can be used for spam.