Forum Discussion
Date calculation in single column
Hello Everyone,
How do I get days between two dates which are arranged vertically in a single column?
The issue is that dates are in a single column and I need to get day 2-day 1 for each rec#.
Please help..???
Excel First make sure that the ID's and dates are in order (they were in your example) by sorting first by ID, then by date. Then add a column to label each date as Day 1 or Day 2.
Now you can make a pivot table of the lot. ID in the Row field, Day in the Column field and Date in the value field (change it to Sum rather than Count) and format as a date. Add the Date again to the value field and set it to "Show data as", "Difference From", "Day", "1". Format the field as General.
Now you can over-write the default headers of the pivot table to something more meaningful, and in this particular case I have hidden column H as it has no relevant data (just blanks).
Now, all of this can probably be done more robust via Power Pivot and/or Power Query, but I haven't looked into that.
9 Replies
Excel Didn't go through the trouble of replicating your screenshot but created a small example. Consider using a pivot table to create the list how I understood you need it. See attached.
- Sir, can you please explain how to solve in Pivot Table?
Excel It's like the one I attached. But if your real data isn't as simple as in my example better upload a file. Then I can try to create the table for you and explain what I did.
