Pivot Table with Multiple Dates

Copper Contributor

Hello guys...hoping you can help me here.

I want to create a pivot table where I can see at once the current number of man-days the worker has consumed. The workers are on-call, hence there are so many arrivals and departures.





Worker 1      Jan. 15, 2023           March 15, 2023           Inactive             40

Worker 1      April 1, 2023           May 31, 2023              Inactive             20

Worker 1      June 15, 2023                                             Active                70

Worker 2     Jan. 20, 2023                                               Active               100

Worker 3    Jan. 20, 2023          Jan. 31, 2023                Inactive              5


When I created the Pivot table, it shows like this.


Worker 1      Jan. 15, 2023                     May 31, 2023                    Inactive             60

                    June 15, 2023                    Jan. 0, 1990                       Active               70

Worker 2      Jan. 20, 2023                     Jan. 0, 1990                      Active              100

Worker 3     Jan. 20, 2023                       Jan. 31, 2023                   Inactive             5


Since Worker 1 has 2 Status on record, his name has 2 lines also. I want to have one line only showing the latest Status.


Worker 1             Jan. 15, 2023                                                      Active             130


Jan. 0, 1990 is a result because the cell is blank as the Worker is still active. No departure date yet. How to make it blank as well.


Thank you all.

8 Replies

Hi @DeejayJohn,

You can try these steps to make a Pivot table with multiple dates:

Step 1: Create a new pivot table

  1. Select the data that you want to include in the pivot table.
  2. Click the Insert tab.
  3. In the Tables group, click PivotTable.
  4. In the Create PivotTable dialog box, select the New Worksheet option.
  5. Click OK to create the pivot table.

Step 2: Add the required fields to the pivot table

  1. Drag the NAME field to the Row Labels area of the pivot table.
  2. Drag the STATUS field to the Filters area of the pivot table.
  3. In the Filters area, click the All checkbox next to the STATUS field.
  4. Clear the checkbox next to the Inactive status.
  5. Click OK to apply the filter.
  6. Drag the ARRIVAL DATE field to the Values area of the pivot table.
  7. Change the value calculation for the ARRIVAL DATE field to Maximum.
  8. Drag the MAN-DAYS field to the Values area of the pivot table.
  9. Change the value calculation for the MAN-DAYS field to Sum.

Step 3: Calculate the total number of man-days consumed to date

To calculate the total number of man-days consumed to date, you can use the following calculated field:


Total Man-Days Consumed to Date =



This calculated field will return the total number of man-days consumed to date, even for workers who are still active.

To add this calculated field to the pivot table, follow these steps:

  1. Click the PivotTable Analyze tab.
  2. In the Calculations group, click Calculated Field.
  3. In the Calculated Field dialog box, enter the following information:
    • Name: Total Man-Days Consumed to Date
    • Formula: IF([STATUS] = "Active", [MAX of ARRIVAL DATE] + [MAN-DAYS], [MAN-DAYS])
  4. Click OK to add the calculated field to the pivot table.
  5. Drag the Total Man-Days Consumed to Date field to the Values area of the pivot table.

Step 4: Remove the duplicate rows

To remove the duplicate rows from the pivot table, you can use the following filter:

  1. In the PivotTable Analyze tab, click the Filter button.
  2. In the Filters pane, click the Total Man-Days Consumed to Date field.
  3. In the Filter dialog box, click the Unique Values checkbox.
  4. Click OK to apply the filter.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic


Depends on your Excel platform/version. Creating PivotTable you may add data to data model, add DAX measures like

Arrival:=MIN( daysConsumed[ARRIVAL DATE] )
Departure:=IF( "Active" IN VALUES(daysConsumed[STATUS]), BLANK(), MAX(daysConsumed[DEPARTURE DATE]) )
ManDays:=SUM( daysConsumed[MAN-DAYS] )
Current Status:=IF( "Active" IN VALUES(daysConsumed[STATUS]), "Active",  "Inactive" )

and use them creating PivotTable




Thanks. I got an idea on what steps to follow.

I'm just having problem on the formula in calculated field.

Thanks Sergei.
This is the output I want to have.

But I don't know how to use your formula.

I am using Excel 365.

best response confirmed by DeejayJohn (Copper Contributor)


Creating PivotTable add data to data model


Add Name to Rows.

In PivotTable pane right click on table name and Add measure


add measures one by one


Finalize PivotTable with them






latest status 2023/6/15?

Worker 12023/6/15 Active130
Worker 22023/1/20 Active100
Worker 32023/1/202023/1/31Inactive5


create temp table aa as
select f01,f02,f03,f04,sum(f05) total,max(f02) m from showing_the_latest_Status group by f01;
select colExclude[m] from aa;

Thanks again. I'm almost there!

2 Things!

1. MIN of ARRIVAL does not show
2. There is a 3rd option on the STATUS. A Worker is ON LEAVE if he just termporarily left for R&R. I cannot add it in the formula because it says maximum of 3 arguments only.

Please help.


Arrival shows MIN ARRIVAl date


Didn't catch how ON LEAVE shall work, it's just possible latest status together with Active or not. Perhaps

Current Status:=VAR statuses=VALUES(daysConsumed[STATUS])
 RETURN IF("On leave" IN statuses,
           "On leave",
           IF( "Active" IN statuses, "Active",  "Inactive" ) )


If you generate small sample file with possible cases is source and desired answer it'll be easier to check the logic.