Subtracting dates

**excelamateur!** · 09-13-2009, 04:51 PM

I've got a table (5 columns) with a list of dates. I want to subtract the dates in each column. I've no problem with subtracting the dates per se, however here's the problem.

I need to subtract them in ascending order i.e the largest minus the 2nd largest, the 2nd largest minus the 3rd largest etc. However there is no order of the dates across the 5 columns. I.e on some rows, the largest will be in column 3, on others it will be in column 1.

Any ideas?

I've attached a file with the typical table on the LH side. On the RH side is what I need to get out of it, however I had to create this manually. I've got a very big list so I need a formula, macro that can do it for me.

Help gratefully received

!

**shg** · 09-13-2009, 05:14 PM

In G5 and copy across and down,

=B5 - LARGE($A5:$E5, RANK(B5, $A5:$E5) + 1)

K5 and down, =MAX(A5:E5) - MIN(A5:E5)

**excelamateur!** · 09-14-2009, 04:20 PM

Works a treat....never would have got there on my own. Introduced me to a new function (rank)....Many thanks

**husni** · 02-19-2014, 04:05 AM

I got another issue too here...

Subtract these 2 dates, exclude Sundays & Holidays (in a list)

And I'm outside from US.

Eg:
DATE ISSUE DATE CLOSED
A B
21/1/2013 21/2/2013
14/2/2013 28/5/2013

Is someone could help me??

**Fotis1991** · 02-19-2014, 04:43 AM

Pls take a look to forum rules.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

And as i see you have already created your own thread.....