Subindexing and Marginal Differencing

dfdsiii

New Member
Joined
Sep 10, 2019
Messages
1
I have this dataset:
UserDateTime
A9/1/20198:00 AM
A9/1/201910:00 AM
A9/1/20194:00 PM
A9/1/20195:00 PM
A9/2/20198:00 AM
A9/2/20195:00 PM
B9/1/20199:00 AM
B9/1/20193:00 PM
B9/1/20195:00 PM

<tbody>
</tbody>
The first thing I want to do is add a subindex on User and Date.
UserDateTimeUser/Date Index
A9/1/20198:00 AM1
A9/1/201910:00 AM2
A9/1/20194:00 PM3
A9/1/20195:00 PM4
A9/2/20198:00 AM1
A9/2/20195:00 PM2
B9/1/20199:00 AM1
B9/1/20193:00 PM2
B9/1/20195:00 PM3

<tbody>
</tbody>
The second thing I want is a column that takes the difference between times in its row (A) and the row after it (A+1). "n/a" means I don't really care about this value. I just want the interday marginal differences.
UserDateTimeUser/Date IndexMarginal Difference
A9/1/20198:00 AM102:00:00
A9/1/201910:00 AM206:00:00
A9/1/20194:00 PM301:00:00
A9/1/20195:00 PM4n/a
A9/2/20198:00 AM109:00:00
A9/2/20195:00 PM2n/a
B9/1/20199:00 AM106:00:00
B9/1/20193:00 PM202:00:00
B9/1/20195:00 PM3n/a

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,883
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">User</td><td style=";">Date</td><td style=";">Time</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">8:00 AM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">02:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">10:00 AM</td><td style="text-align: right;;">2</td><td style="text-align: right;;">06:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">3</td><td style="text-align: right;;">01:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">A</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">4</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">A</td><td style="text-align: right;;">09/02/2019</td><td style="text-align: right;;">8:00 AM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">09:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">A</td><td style="text-align: right;;">09/02/2019</td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">B</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">9:00 AM</td><td style="text-align: right;;">1</td><td style="text-align: right;;">06:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">3:00 PM</td><td style="text-align: right;;">2</td><td style="text-align: right;;">02:00:00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">B</td><td style="text-align: right;;">09/01/2019</td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">3</td><td style=";"></td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">A$2:A2,A2,B$2:B2,B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">D3>D2,C3-C2,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,089,992
Messages
5,411,713
Members
403,394
Latest member
Mohan Kumar

This Week's Hot Topics

Top