Date Calculation

[tinferns]

Hi,
Would it be possible to calculate difference between 2 or more dates in the same Cell ?
Dates are in the format shown below (Always seperated by a comma):
Cell A1 = 29Jan2009, 31Jan2009, 14Feb2009, 27Feb2009, 31Mar2009.
In the above case there are only 5 sets of dates, in some cases there are more and in some cases there are less. Between Min 2 dates - Max 7 dates.
Am looking for a result like : 2,14,13,12
Am not well versed with VBA, hence would prefer a formula. Currently am doing Text to Column and working on the same. And that process is time consuming. Would there be a short cut ?
Await reply.
Thanks in advance.

Martin

 

[SteveO59L]

In some spare columns off to the right, split your dates into seperate cells using mid(), calculate the differences then concatenate the differences into one cell.

 

[tinferns]

Hi Steve.... thanks for your prompt response..

That is exactly what I am currently doing (text to columns).. was wondering if there cud be a shorter way out..

cheers.. Martin..

 

[SteveO59L]

Not using text to columns, use mid()

With your source in A1

in C1 =MID($A$1,1,9)*1
in D1 =MID($A$1,12,9)*1
in E1 =MID($A$1,23,9)*1
in F1 =MID($A$1,34,9)*1
in G1 =MID($A$1,45,9)*1

splits out each date.

in

I1 =+D1-C1
J1 =+E1-D1 etc ..........


works out the difference

end in

B1 =I1&", "&J1&", "&K1&", "&L1


concatenates the difference values back to one cell

 

[barry houdini]

How is a date like 1st January represented, is there a leading zero like 01Jan2009?

 

[tinferns]

Hi,
Yes 01Jan2009 is the fromat used.

thanks

 

[barry houdini]

If your data is in A1 then this formula should give you the requested result for up to 7 dates

=TEXT(SUMPRODUCT(MID(A1,(ROW(INDIRECT("1:"&(LEN(A1)+2)/11-1)))*11+1,9)-MID(A1,(ROW(INDIRECT("1:"&(LEN(A1)+2)/11-1))-1)*11+1,9)+0,10^(((LEN(A1)+2)/11-1-ROW(INDIRECT("1:"&(LEN(A1)+2)/11-1)))*2)),"##, ##, ##, ##, ##, ##")

...but I'm assuming there will only be up to double figure date differences (up to 99 days). Could that be higher?

Note: I assume there's no "full stop" at the end of the data....

 

[tinferns]

Hi Barry,
Thanks for the reply..

Yes, there will be times when there is a difference of more than 100 days. can that be sorted ?


There is no Full Stop at the end of the last date. Sorry for the confusion.

Await your reply..

Thanks

Martin

 

[barry houdini]

OK, then, you can just use a formula to concatenate the differences, i.e.

=MID(A1,12,9)-MID(A1,1,9)&IF(LEN(A1)>23,", "&MID(A1,23,9)-MID(A1,12,9),"")&IF(LEN(A1)>34,", "&MID(A1,34,9)-MID(A1,23,9),"")&IF(LEN(A1)>45,", "&MID(A1,45,9)-MID(A1,34,9),"")&IF(LEN(A1)>56,", "&MID(A1,56,9)-MID(A1,45,9),"")&IF(LEN(A1)>67,", "&MID(A1,67,9)-MID(A1,56,9),"")

 

[tinferns]

Thanks Barry,

It works perfectly fine.. cheers... Martin