Home

Pivot table to get number of working days

%3CLINGO-SUB%20id%3D%22lingo-sub-887902%22%20slang%3D%22en-US%22%3EPivot%20table%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887902%22%20slang%3D%22en-US%22%3E%3CP%3Ehere%20attached%20excel%20workbook%20which%20i%20need%20to%20get%20number%20of%20machines%20work%20on%20a%20day%20as%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etable%201%20is%20data%20table%20.%20table%202%20is%20pivot%20table%20which%20i%20got%20with%20extract%20date%20field%20and%20table%203%20is%20the%20format%20what%20i%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoutput%200%20means%20that%20machine%20not%20work%20on%20that%20relevant%20date.%20anyone%20can%20help%20me%20to%20get%20that%20as%20per%20the%20table%203%20format%20except%20machine%20names%20and%20output%20%220%22%20details.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-887902%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888431%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417958%22%20target%3D%22_blank%22%3E%40RavinduF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20duplication%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FExcel-formula-to-get-number-of-working-days%2Fm-p%2F887899%23M41178%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FExcel-formula-to-get-number-of-working-days%2Fm-p%2F887899%23M41178%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnyway%2C%20the%20measure%20is%20the%20same%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ENumber%20of%20machines%20work%3A%3DCALCULATE(COUNTROWS(Range)%2CRange%5BOutput%20MC%5D%26gt%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888453%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eappreciate%20your%20support.%20i%20don't%20have%20power%20pivot%20option%20to%20do%20this%20with%20my%202013%20limited%20version.%20is%20there%20any%20other%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888496%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417958%22%20target%3D%22_blank%22%3E%40RavinduF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20don't%20need%20Power%20Pivot%2C%20it%20is%20enough%20that%20you%20have%20data%20model%20which%20shall%20be%20with%202013.%20You%20may%20add%20measure%20without%20Power%20Pivot.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnyway%2C%20with%20formula%20if%20you%20add%20Date%20column%20manually%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20298px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135084iF7265383DB7E2B9D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethe%20formula%20in%20next%20column%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTIFS(%24A%244%3A%24A%2457%2C%24Q6%2C%24C4%3A%24C57%2C%22%26gt%3B0%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
RavinduF
Occasional Contributor

here attached excel workbook which i need to get number of machines work on a day as a pivot table.

 

table 1 is data table . table 2 is pivot table which i got with extract date field and table 3 is the format what i need.

 

output 0 means that machine not work on that relevant date. anyone can help me to get that as per the table 3 format except machine names and output "0" details. 

3 Replies

@RavinduF 

It looks like duplication of https://techcommunity.microsoft.com/t5/Excel/Excel-formula-to-get-number-of-working-days/m-p/887899#...

 

Anyway, the measure is the same

Number of machines work:=CALCULATE(COUNTROWS(Range),Range[Output MC]>0)

@Sergei Baklan 

appreciate your support. i don't have power pivot option to do this with my 2013 limited version. is there any other way to do this?

 

 

Highlighted

@RavinduF 

You don't need Power Pivot, it is enough that you have data model which shall be with 2013. You may add measure without Power Pivot.

 

Anyway, with formula if you add Date column manually

image.png

the formula in next column is

=COUNTIFS($A$4:$A$57,$Q6,$C4:$C57,">0")
Related Conversations
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Problems using Pivot tables in Excel 16.25 on Mac
jeromepineau in Excel on
0 Replies