shared with you a month that the holiday is automatically displayed according to the holiday. Let’s look at the holiday schedule announced by the General Office of the State Council.
The days after each holiday are marked with the number of days off. Then I want to know how Do you know how to use Excel to quickly achieve the number of working days in a month?
will share with you today how to quickly calculate the number of days in a month
operation steps:
1, first, we first make a table of the start date and end date of each month and the number of working days, as shown below:
the start date and end of this table Date making method, and then we will popularize two tips
skills 1. Start date making
First we enter the date of the first month in cell A2: 2020/1/, then we select the area A2:A13, click 【 Start]-[Fill]-[Sequence]-[Column]-[Date]-[Month] , that is, automatically generate the initial date of each month
Skills 2. End date production
We enter the formula in cell B2:
=EOMONTH (A2,0)
EOMONTH function can realize the calculation of the last day of the month before or after the specified date
If we change the formula to
=EOMONTH(A2,2)
, the last day of the March copy is generated
3. Preparation work Finished, we will start to calculate the work date. If you have read our previous article
Workday series, powerful date functions, you deserve to have
to calculate the number of working days between two dates, we can use the NETWORKDAYS function
NETWORKDAYS(start_date,end_date ,[holidays])
where Start_date must represent the start date, End_date must represent the end date, excluding the days of weekends and holidays
Holidays is optional, is an optional list, contains one or more dates that need to be excluded from the work calendar
Looking at the holiday and make-up schedule above, we can see that the make-up dates are distributed in different months, so we can first calculate the number of days in the month corresponding to the make-up date. You can use the COUNTIFS function
to enter the formula in D2:
=COUNTIFS (H$2:H$8,">="&A2,H$2:H$8,"<="&b>
CONTIFS function realizes the calculation of the number of satisfying conditions in multiple areas
and then we enter the formula in C2:
=NETWORKDAYS(A2,B2,G$2:G$28)+D2
z
z2 can see all the work completed in the number of days 0zz
z2 Now, we can combine the formula in column D and the formula in column C into:
=NETWORKDAYS(A2,B2,G$2:G$28)+COUNTIFS(H$2:H$8,">="&A2,H$2: H$8,"<="&B2)
removes the holiday, removes Saturdays and Sundays, and adds the list of days of make-up work. We can optimize the table again, delete the auxiliary calculation process in the middle, and finally keep it This kind of data will do
If you think the article is helpful to you, I hope everyone can help like and share it~, thank you
This article is original by Rainbow Excel, welcome to pay attention, and bring you a long knowledge!
For more content, please pay attention to "Rainbow Excel", thank you~~
