#1   Report Post  
Maxi
 
Posts: n/a
Default Date difference

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

  #2   Report Post  
Niek Otten
 
Posts: n/a
Default

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

"Maxi" wrote in message
ups.com...
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



  #3   Report Post  
Roger Govier
 
Posts: n/a
Default

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

--
Regards
Roger Govier
"Maxi" wrote in message
ups.com...
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



  #4   Report Post  
Maxi
 
Posts: n/a
Default

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

  #5   Report Post  
Maxi
 
Posts: n/a
Default

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



  #6   Report Post  
bj
 
Posts: n/a
Default

I think the wrapping is screwy
but try something like
=datevalue(indirect("R"&counta(A1:I1)&"C"&row(),Fa lse))-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" wrote:

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


  #7   Report Post  
Maxi
 
Posts: n/a
Default

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

  #8   Report Post  
bj
 
Posts: n/a
Default

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

"Maxi" wrote:

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
Date difference taking into account weekends. annonymous Excel Worksheet Functions 3 March 14th 05 06:35 PM
Calculating Date difference in 2 ways Hari Excel Discussion (Misc queries) 5 January 15th 05 11:25 PM
Difference of date Atif New Users to Excel 5 January 6th 05 11:53 PM
Difference of date & time in minutes ramsdesk Excel Worksheet Functions 2 November 10th 04 07:01 AM


All times are GMT +1. The time now is 08:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"