Date difference

[Maxi]

2-Dec-04 2-Feb-05 2-Mar-05 15-Mar-05 27-Apr-05 5-May-05 17-May-05
6-Jun-05 7-Jun-05
4-Jun-04 5-Nov-04 2-Mar-05 2-Mar-05 4-Apr-05
2-Oct-04 1-Jan-05 1-Jan-05 2-Jun-05 5-Jun-05 1-Jul-05 1-Jul-05
1-Jul-05

I have the above dates in range A1:I3.

I need formula in K1:M3. The answers are in range K1:M3 should be:

51 52 72
115 148 148
27 27 27

I want to find out last date in the row and then see its difference
from today's date (current date per computer time) and put it in cell
K1

Similarly the second last date difference from current date in L1 and
third last date difference from current date in M1.

Do the same task for remaining columns.

This is just a sample data I have 3160 rows with 25+ columns

I have entered a formula but I am getting stuck on how to find the last
date in a row.

Maxi

 

[Niek Otten]

Hi Maxi,

Difficult to reproduce your results: due to wrapping it is not clear which
data is in which line. It's certainly not A1:I3 completely filled.
Can you specify?

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Roger Govier]

Hi Maxi
In K1
=TODAY()-LARGE(A$1:I$1,COLUMN()-10)
Copy through L1:M1
Copy K1:M1 through K2:K3

 

[Maxi]

Okay 1st line is wrapped. It has 9 dates from 2-Dec-04 to 7-Jun-05
2nd line is NOT wrapped. It has 5 dates from 4-Jun-04 to 4-Apr-05
3rd line is wrapped. It has 8 dates from 2-Oct-04 to 1-Jul-05

Maxi

 

[Maxi]

Got it

K1 = =TODAY()-INDEX($A1:$I1,COUNT($A1:$I1))
L1 = =TODAY()-INDEX($A1:$I1,COUNT($A1:$I1)-1)
M1 = =TODAY()-INDEX($A1:$I1,COUNT($A1:$I1)-2)

Scroll down

Thanx
Maxi

 

[bj]

I think the wrapping is screwy
but try something like
=datevalue(indirect("R"&counta(A1:I1)&"C"&row(),False))-today() for K1
=datevalue(indirect("R"&(counta(A1:I1)-1)&"C"&row(),False))-today() for L1
=datevalue(indirect("R"&(counta(A1:I1)-2)&"C"&row(),False))-today() for M1
just change the counta range to go to other rows..

 

[Maxi]

We have all different formulas giving correct results.

Which formula will take less system resources and will do the task with
a great speed compared to the other two formulas?

Maxi

 

[bj]

I really do not know, but I would bet Roger's method would be faster and take
fewer resources.