VBA to Calculate Date Difference in Days

[wazzulu1]

Hi;

I have a spreadsheet that I need to calculate the date difference in days between each row that has the same LogIn. So I need the date difference in days between row 1 & 2, then 2 & 3, because the LogIn is the same, but row 4 has a new LogIn that would need to begin. The sort would be on the LogIn, then by date in ascending order. I pasted some sample data, just not sure how to do this with VBA.

Appreciate any insight on this if possible, days would be posted in column c.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]LogIn[/TD]
[TD="class: xl67, width: 64"]JP Date[/TD]
[/TR]
[TR]
[TD="class: xl65"]6122218[/TD]
[TD="class: xl66, align: right"]1/7/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6122218[/TD]
[TD="class: xl66, align: right"]5/1/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6122218[/TD]
[TD="class: xl66, align: right"]5/1/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6110416[/TD]
[TD="class: xl66, align: right"]9/14/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6110416[/TD]
[TD="class: xl66, align: right"]7/1/2010[/TD]
[/TR]
[TR]
[TD="class: xl65"]6745793[/TD]
[TD="class: xl66, align: right"]1/26/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6745793[/TD]
[TD="class: xl66, align: right"]1/26/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6745045[/TD]
[TD="class: xl66, align: right"]1/11/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6745045[/TD]
[TD="class: xl66, align: right"]11/1/2010[/TD]
[/TR]
[TR]
[TD="class: xl65"]6745045[/TD]
[TD="class: xl66, align: right"]3/19/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6741547[/TD]
[TD="class: xl66, align: right"]10/13/2007[/TD]
[/TR]
[TR]
[TD="class: xl65"]6741547[/TD]
[TD="class: xl66, align: right"]5/28/2014[/TD]
[/TR]
[TR]
[TD="class: xl65"]6741056[/TD]
[TD="class: xl66, align: right"]5/11/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6741056[/TD]
[TD="class: xl66, align: right"]7/1/2012[/TD]
[/TR]
[TR]
[TD="class: xl65"]6747483[/TD]
[TD="class: xl66, align: right"]2/16/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6747483[/TD]
[TD="class: xl66, align: right"]8/1/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6747483[/TD]
[TD="class: xl66, align: right"]7/5/2016[/TD]
[/TR]
[TR]
[TD="class: xl65"]6747483[/TD]
[TD="class: xl66, align: right"]7/5/2016[/TD]
[/TR]
[TR]
[TD="class: xl65"]6749505[/TD]
[TD="class: xl66, align: right"]12/13/2008[/TD]
[/TR]
[TR]
[TD="class: xl65"]6749505[/TD]
[TD="class: xl66, align: right"]10/4/2010[/TD]
[/TR]
[TR]
[TD="class: xl65"]6749505[/TD]
[TD="class: xl66, align: right"]10/28/2015[/TD]
[/TR]
[TR]
[TD="class: xl65"]6775892[/TD]
[TD="class: xl66, align: right"]12/13/2008[/TD]
[/TR]
[TR]
[TD="class: xl65"]6775892[/TD]
[TD="class: xl66, align: right"]8/21/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6775196[/TD]
[TD="class: xl66, align: right"]5/16/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6775196[/TD]
[TD="class: xl66, align: right"]6/4/2012[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773328[/TD]
[TD="class: xl66, align: right"]2/6/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773328[/TD]
[TD="class: xl66, align: right"]8/4/2010[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773328[/TD]
[TD="class: xl66, align: right"]5/1/2012[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773328[/TD]
[TD="class: xl66, align: right"]3/1/2014[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773620[/TD]
[TD="class: xl66, align: right"]1/5/2009[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773620[/TD]
[TD="class: xl66, align: right"]7/5/2010[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773620[/TD]
[TD="class: xl66, align: right"]2/1/2011[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773620[/TD]
[TD="class: xl66, align: right"]3/22/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773620[/TD]
[TD="class: xl66, align: right"]11/1/2013[/TD]
[/TR]
[TR]
[TD="class: xl65"]6773620[/TD]
[TD="class: xl66, align: right"]11/1/2013[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Got your heart set on VBA rather than a simple formula?

 

[wazzulu1]

I have my heart set on whatever will work.

Can a formula distinguish the value in column A to correctly calculate the data difference?

 

[shg]

Sort by JP Date, then

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

LogIn​

[/td][td="bgcolor:#F3F3F3"]

JP Date​

[/td][td="bgcolor:#F3F3F3"]

Days​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

6741547​

[/td][td]

10/13/2007​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td]C2: =IFERROR(B2 - LOOKUP(9E+307, B$1:B1 / (A$1:A1=A2)), "none prior")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

6749505​

[/td][td]

12/13/2008​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

6775892​

[/td][td]

12/13/2008​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

6773620​

[/td][td]

1/5/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

6745045​

[/td][td]

1/11/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

6745793​

[/td][td]

1/26/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

6745793​

[/td][td]

1/26/2009​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

6773328​

[/td][td]

2/6/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

6747483​

[/td][td]

2/16/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

6741056​

[/td][td]

5/11/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

6775196​

[/td][td]

5/16/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

6110416​

[/td][td]

9/14/2009​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

6110416​

[/td][td]

7/1/2010​

[/td][td="bgcolor:#E5E5E5"]

290​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

6773620​

[/td][td]

7/5/2010​

[/td][td="bgcolor:#E5E5E5"]

546​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

6773328​

[/td][td]

8/4/2010​

[/td][td="bgcolor:#E5E5E5"]

544​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

6749505​

[/td][td]

10/4/2010​

[/td][td="bgcolor:#E5E5E5"]

660​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

6745045​

[/td][td]

11/1/2010​

[/td][td="bgcolor:#E5E5E5"]

659​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

6773620​

[/td][td]

2/1/2011​

[/td][td="bgcolor:#E5E5E5"]

211​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

6773328​

[/td][td]

5/1/2012​

[/td][td="bgcolor:#E5E5E5"]

636​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

6775196​

[/td][td]

6/4/2012​

[/td][td="bgcolor:#E5E5E5"]

1115​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

6741056​

[/td][td]

7/1/2012​

[/td][td="bgcolor:#E5E5E5"]

1147​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

6122218​

[/td][td]

1/7/2013​

[/td][td="bgcolor:#E5E5E5"]none prior[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

6745045​

[/td][td]

3/19/2013​

[/td][td="bgcolor:#E5E5E5"]

869​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]

6773620​

[/td][td]

3/22/2013​

[/td][td="bgcolor:#E5E5E5"]

780​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]

6122218​

[/td][td]

5/1/2013​

[/td][td="bgcolor:#E5E5E5"]

114​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]

6122218​

[/td][td]

5/1/2013​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]

6747483​

[/td][td]

8/1/2013​

[/td][td="bgcolor:#E5E5E5"]

1627​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]

6775892​

[/td][td]

8/21/2013​

[/td][td="bgcolor:#E5E5E5"]

1712​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]

6773620​

[/td][td]

11/1/2013​

[/td][td="bgcolor:#E5E5E5"]

224​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]

6773620​

[/td][td]

11/1/2013​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]

6773328​

[/td][td]

3/1/2014​

[/td][td="bgcolor:#E5E5E5"]

669​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]

6741547​

[/td][td]

5/28/2014​

[/td][td="bgcolor:#E5E5E5"]

2419​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td]

6749505​

[/td][td]

10/28/2015​

[/td][td="bgcolor:#E5E5E5"]

1850​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]

6747483​

[/td][td]

7/5/2016​

[/td][td="bgcolor:#E5E5E5"]

1069​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td]

6747483​

[/td][td]

7/5/2016​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[/table]

 

[wazzulu1]

This works great, Thanks!!!

I just assumed that VBA was needed

 

[shg]

You're welcome.

 

[Marin30]

hello


I CON NOT FIND TO CREAT NEW POST

HOW I CAN ATTACH PICHER?

THANK YOU