Search code examples
exceldateconsolidation

How to consolidate dates in Excel?

I have two columns. One with the dates, and other with the counts. It looks like this: 

Stamp:.................... Views
13-12-2013 9:00.... 2
13-12-2013 9:00.... 8 
13-12-2013 10:00... 4
14-12-2013 10:00... 4
14-12-2013 10:00... 11

When I consolidate this, I get weird values like this: 

2
41621,375   8
41621,41667 4
41622,41667 11

It seems that Excel is calculating the dates. I would like to prevent that. I searched the whole internet for any solutions, but found no final answer.

Suggestions?

Solution

  • I got consolidate to work on data with dates. But I needed to make a new tab with formulas that refer to the original tab. So I would start by making a new tab with the headings of:

    1. Stamp (text)
    2. Views

    Then, under the Views heading, add a formula (in B2) referencing the data in the original tab's B2:

        ='<original tab name>'!B2

    Then drag that formula down as far as you need to so all the data is present. Then, under the Stamp (text) heading, add a formula (in A2) to convert the date data to text:

        =TEXT('<original tab name'!A2,"dd-mm-yyyy H:MM")

    Once again, drag that formula down as far as you need to so all the data is present. Then consolidate on the new tab. I did the following:

    1. Click on D1
    2. Click Consolidate
    3. Select A1:B6 (all the data including headers)
    4. Make sure Top row and Left column are checked
    5. Click OK

    The correct consolidated data is inserted at D1.

    I used this link as a reference for how to use the text formula.

    I hope this helps!