Pivot Table with Multiple Dates

[DeejayJohn]

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.

Example.

NAME           ARRIVAL DATE       DEPARTURE DATE        STATUS       MAN-DAYS 

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.

NAME      Min. of ARRIVAL DATE    Max of DEPARTURE DATE   STATUS     Sum of MAN-DAYS

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.

[#2]

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 =
IF([STATUS] = "Active", [MAX of ARRIVAL DATE] + [MAN-DAYS], [MAN-DAYS])

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
(LinkedIn)

[#3]

@DeejayJohn 

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

[#4]

@LeonPavesic 

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

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

[#5]

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.

[#6 · Accepted solution]

@DeejayJohn 

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

[#7]

latest status 2023/6/15？

Worker 1	2023/6/15		Active	130
Worker 2	2023/1/20		Active	100
Worker 3	2023/1/20	2023/1/31	Inactive	5

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

[#8]

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.

[#9]

@DeejayJohn 

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.