Work out elapsed time spent at a location

[chakyt]

I am trying to find out how long (in days) a ship is spending at a specific location by analysing satellite data.


This would be easy if the ship gave out consistent data, but due to technical issues it gives out inconsistent signals giving incorrect results. The satellite data also gives time data, but I’m not interested in that, only dates - hence the duplicate 02/12/14.


Is there any way to take a subtotal count of location A (e.g. 5 satellite signals) as well as working out the elapsed days between 02/12 > 18/12 (e.g.: 16 days) spent at location A?

Thanks!

 

[RonB1111]

This is a CSE formula which requires you to confirm it by hitting the CTRL-Shift-ENTER keys simultaneously (do not just hit the ENTER key):
=MAX(IF(A4:A11="A",B4:B11))-MIN(IF(A4:A11="A",B4:B11))

 

[chakyt]

Hi RonB1111,

Thanks very much for your reply! Unfortunately that formula is returning "0". I've pressed Ctrl-shift-enter but doesn't seem to work.
Can I maybe send my spreadsheet to you so you can have a look?
http://i.imgur.com/62v5WEu.png

Thanks,
Chakyt

 

[Stridhan]

Have you changed the lookupvalue "A" in the formula to cellreference, A2 instead?

 

[chakyt]

Hi Stridhan,
This is what I'm actually working with. I've tried the formula, and I did change the lookup value to look up the locations. Is that correct?
(Longitude is the location)
http://i.imgur.com/62v5WEu.png
Thanks!

 

[RonB1111]

Change "100.13" to 100.13 without the quotes in both instances in your formula. The 100.13 is a numeric value in column A. Only use the quotation marks if it is text.

 

[chakyt]

Hi RonB1111,
I've done that now. Still returning 0?
http://i.imgur.com/3gsYoaN.png

Thanks again,
Chakyt

 

[Stridhan]

what if you´re changing column A to B instead. You seem to have a rounding on the location, perhaps location A contains more decimals.

 

[RonB1111]

Please verify a couple things:
1. Make sure the 100.13 in A2 is not, for instance 100.12895. (in format cells, increase the number of decimals to verify its 100.13000000)
2. Make sure the dates in Col D are in Excel Date Format, and not text in the form of 2014-12-14 (if you format the cell as a Number, it should display 41,997)

 

[chakyt]

Yeh, I thought that might make a difference so I've gotten rid of that column and am just using the visible values now. How about this? It's still returning 0 though...