Home

Excel formula to get number of working days

%3CLINGO-SUB%20id%3D%22lingo-sub-885604%22%20slang%3D%22en-US%22%3EExcel%20formula%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885604%22%20slang%3D%22en-US%22%3E%3CP%3Ehere%20attached%20my%20workbook%20.%20i%20have%20data%20table%20and%20i%20have%20make%20a%20pivot%20table%20also%20for%20it.%26nbsp%3B%3C%2FP%3E%3CP%3Ethere%20are%20machine%20names%20entry%20days%20and%20output.%20every%20day%20repeat%20all%20machine%20names%20but%20not%20work%20all%20machines.%20i%20need%20to%20get%20number%20of%20machines%20work%20on%20a%20day%20by%20day%20on%20new%20column%20in%20my%20pivot%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-885604%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%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%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-885792%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885792%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%3CP%3EPlease%20find%20the%20attached%20with%20the%20two%20Pivot%20Tables%20and%20see%20if%20any%20one%20of%20them%20is%20what%20you%20are%20trying%20to%20achieve.%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-887868%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20for%20the%20reply.%20this%20is%20not%20what%20i%20need.%20i%20need%20a%20pivot%20table%20just%20like%20your%201st%20pivot%20without%20extracting%20dates%20based%20on%20machine%20names.%20there%20should%20be%20only%206%20rows%20for%206%20days%20and%20only%20machine%20count%20work%20on%20this%20relevant%20date%20(%20as%20a%20example%20except%20%220%22%20output%20on%20output%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887899%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887899%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%3CP%3EForget%20about%20the%20Pivot%20Table%2C%20just%20mock%20up%20the%20desired%20output%20manually%20based%20on%20the%20sample%20data%20in%20the%20file%20to%20show%20what%20exactly%20you%20are%20trying%20to%20achieve%20and%20upload%20the%20file%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887922%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887922%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20like%20a%20pivot%20table%20like%20table%203%20on%20the%20attachment%20without%20extracting%20data%20and%20except%20%220%22%20on%20output%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888003%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20get%20number%20of%20working%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888003%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%3CP%3EWhile%20inserting%20the%20Pivot%20Table%2C%20on%20Create%20Pivot%20Table%20dialog%20box%2C%20check%20the%20option%20at%20the%20bottom%20which%20says%20%22Add%20this%20data%20to%20the%20Data%20Model.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20the%20Pivot%20Table%20is%20inserted%20at%20the%20desired%20destination%2C%20right%20click%20on%20Range%20in%20the%20Pivot%20Table%20Fields%20List%20and%20choose%20Add%20Measure%20and%20in%20the%20next%20Measure%20window%2C%20type%20a%20Name%20for%20the%20Measure%20and%20paste%20the%20formula%20given%20below%20in%20the%20Formula%20box%20and%20click%20ok%20to%20finish.%3C%2FP%3E%3CP%3EThen%20drag%20the%20Dates%20in%20the%20Rows%20area%20and%20New%20Measure%20you%20created%20in%20the%20Values%20Area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20for%20Measure%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCALCULATE(COUNTROWS(Range)%2CRange%5BOutput%20MC%5D%26gt%3B0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20attached%20for%20more%20details%20where%20I%20create%20a%20Measure%20called%20%22Number%20of%20Machines%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
RavinduF
Occasional Contributor

here attached my workbook . i have data table and i have make a pivot table also for it. 

there are machine names entry days and output. every day repeat all machine names but not work all machines. i need to get number of machines work on a day by day on new column in my pivot table

5 Replies

@RavinduF 

Please find the attached with the two Pivot Tables and see if any one of them is what you are trying to achieve.

 

 

@Subodh_Tiwari_sktneer 

thank you for the reply. this is not what i need. i need a pivot table just like your 1st pivot without extracting dates based on machine names. there should be only 6 rows for 6 days and only machine count work on this relevant date ( as a example except "0" output on output column.

Highlighted

@RavinduF 

Forget about the Pivot Table, just mock up the desired output manually based on the sample data in the file to show what exactly you are trying to achieve and upload the file again.

@Subodh_Tiwari_sktneer 

thank you!

 

i like a pivot table like table 3 on the attachment without extracting data and except "0" on output

@RavinduF 

While inserting the Pivot Table, on Create Pivot Table dialog box, check the option at the bottom which says "Add this data to the Data Model.

 

Once the Pivot Table is inserted at the desired destination, right click on Range in the Pivot Table Fields List and choose Add Measure and in the next Measure window, type a Name for the Measure and paste the formula given below in the Formula box and click ok to finish.

Then drag the Dates in the Rows area and New Measure you created in the Values Area.

 

Formula for Measure:

=CALCULATE(COUNTROWS(Range),Range[Output MC]>0)

 

Please refer to the attached for more details where I create a Measure called "Number of Machines".

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies