fmpuser0

Calculate Number of Days from Large Date Array

Discussion created by fmpuser0 on Jul 26, 2017
Latest reply on Jul 28, 2017 by fmpuser0

I have a DB of flights with dates, origin cities and destination cities. It can be found at https://goo.gl/oRcZad

 

Each city has a four-character identifier. The first or the first two characters determine a certain geographical area in the world.

 

I need to calculate the number of days spent at each geographical location K, LL, LW and any others. For the calculation, I must include the time that elapses from one flight to the next. For example,

 

01/07 KAAA KBBB

01/13 KBBB KEEE

01/13 KEEE KBBB

01/14 KBBB LWZZ

01/19 LWZZ LLAA

01/22 LLAA LWZZ

 

The number of days in K should be 7, i.e. from 01/07 to 01/14.

The number of days in LW should be 5, i.e. from 01/14 to 01/19.

The number of days in LL should be 3, i.e. from 01/19 to 01/22.

 

Is this possible?

 

Thanks for any help.

Outcomes