How to insert the current date and time in Excel, how to find the number of days between dates (including working days)

On November 24, 2018, the report card was updated to version 2.0, taking into account all your wishes and comments. Download, use, leave your comments under the article.

Hello, dear friends!

Today I will present to your attention my version of the implementation of one of the pressing problems - timesheets in excel . In this article you will find a description of the basic concepts of creating a timesheet, instructions for maintaining it, and you will also be able to download the program using the links at the beginning and end of the article. Download, use, automate your manual labor =).

Building a time sheet in Excel

After downloading and opening the timesheet, you are taken to the main page of the “Settings” construction (Figure 1).

Starting from the second line, you need to fill out the list of employees for whom you will keep timesheets. Indicate the unique employee number, full name, position (optional), working hours in hours (optional). The working day norm is used when calculating overtime and underwork of employees during the month; if you did not fill out this field at the beginning, it can be edited in the constructed timesheet. In cells “F2” and “F3”, use the drop-down list to select the year and month to plot. A drop-down list appears when you select a cell.

After all the data has been entered, click the “Create Timesheet” button and the program will generate a timesheet for the specified month and year (Figure 2).

A time sheet in excel consists of the following areas:

1. The table legend is line 1, which indicates the month and year of construction, as well as the symbols of the timesheet.
2. The list of employees (column “A”), filled out by us on the “Settings” sheet.
3. The header of the table with numbers and days of the week, weekends (Sat and Sun) are highlighted in light green for clarity.
4. The control area in which symbols are written.
5. Calculation columns – columns of formulas for taking into account basic indicators.

Instructions

1. If you need to calculate a certain time interval in Excel, don’t hesitate to use complex mathematical formulas. Most calculations can be done using simple math operations such as addition and subtraction. The main thing is to set the desired cell format.
2. First, you need to select a group of cells in which temporary values ​​will be located. Right-click on the selected group or one cell and in the menu that opens, click on the “Format Cells” command.
3. A new dialog box will be launched, in which you need to go to the “Number” tab. In the left part of this window, select the “Time” item, and the settings for this format will appear on the right side of the window.

   
   Format Cells function
   Select the appropriate type of temporary result and click OK.

4. After assigning a time format to the cells, enter the time values ​​in the required cells in the format XX:XX or XX:XX:XX. Now select the cell to display the result, enter the “Equals” sign in the formula entry field and click on the cell with the final value. Now enter the minus sign and click on the cell with the initial value. Press enter.
5. Example: if you need to calculate the time interval from 11:20:20 to 13:30:00, this data should be entered in cells A1 and B1, respectively, and in the cell with the result (for example, C1) there should be a formula: =B1- A1.
   If the cell format is correct, the result should be 2:9:40. Calculating a time interval If you need to calculate several time values, then simply extend the automatic filling marker from cell C1 to the desired group of results.
6. In order to calculate the sum of all the results obtained, use the standard addition function. Specify the cell in which the overall result should be displayed, select the Fx command, and in the window that loads, specify the SUM option. Label all the cells from which you want to calculate the result. Press the Enter key, the cell with the total value should have the formula: =SUM(C1:C5), if you selected cells C1 to C5. Note that all these cells must be assigned a time format.

We recommend using only a licensed version of Microsoft Excel, which is included in the Microsoft Office 2016 software package. You can buy this program from us in our online store.

Maintaining time sheets in excel

The time sheet must be kept using standard symbols:

• A working day is marked with a number from 1 to 24, which indicates the number of hours worked by the employee per day. The marked day is automatically colored light blue using conditional formatting rules.
• The day of vacation is marked by the abbreviation op . Automatically tints light pink.
• The holiday is marked by the abbreviation pr
  . Automatically tinted with light green color.
• Absenteeism is marked with the designation pg . Automatically tinted gray.
• Time off is indicated by the abbreviation og . Automatically tinted pale orange.
• Leave without pay is indicated by the abbreviation obs . Automatically tinted lilac.
• Study leave is indicated by the letter y . The designation is tinted yellow.
• A business trip is designated by the letter k . Tinted green.
• Sick leave is marked with a small letter b . Automatically tints light yellow.

An example of a completed timesheet in excel can be seen in Figure 3 below:

A formula block of calculated indicators (columns “AG:AJ”) has been added for the convenience of summing up monthly results. This block consists of four columns:

1. Working days – the number of days worked by the employee during the reporting month (number of blue cells).
2. Working hours – the number of hours worked by the employee in the reporting month (the sum of the values ​​of the blue cells).
3. Vacation days – the number of vacation days used by the employee during the reporting month (number of cells marked op ).
4. Sick days – the number of days that the employee spent on sick leave (the number of cells marked b ).
5. Reworked hours - number of processing hours. Counts hours that exceed the daily production rate indicated in the “working” column. day (hour)"
6. Incomplete hours - the number of hours that are not enough to meet the daily production norm. Calculation example: standard 8 hours, costs 6 hours, shortfall - 2 hours; the norm is 8 hours, costs 12 hours, processing - 4 hours; the norm is 8 hours, costs 1st day 6 hours, 2nd day 10 hours, overtime - 0 hours, underwork - 0 hours.
7. Time off - the number of days on which the employee took time off (number of og
   ).
8. Holiday days - number of holidays in a month. Holidays are marked by the abbreviation pr .
9. Absenteeism days - number of absenteeism, marked by the abbreviation pg .
10. Otp. without content — the number of days that the employee spent on vacation without pay. Designation - obs .
11. Study leave - number of days of study leave, designation y .
12. Days commander. — number of days the employee spent on a business trip. Designation to .

WORKDAY function.

It returns a point in time that will occur in a certain number of working days (or occurred earlier). It does not take into account rest days or any holidays that you specify separately.

It is suitable for calculating production schedules and deadlines based on a standard production calendar, with Saturday and Sunday always being rest days for it.

WORKDAY is a built-in function in Excel 2007, 2010, 2013 and 2016. It can be entered manually or using the Function Wizard. In earlier versions, you need to specifically enable the analysis tools package.

You need to use the following arguments:

WORKDAY(start_date, days, [holidays])

The first two are required, and the last is optional:

• Start_date – this is where the counting of days begins.
• Days are the working days to be added or subtracted from the initial reference point. A positive number returns a future point in time, a negative number returns to the past.
• Holiday_days is an optional list of dates that should be considered non-working days. This can be either a range of cells containing dates that you want to exclude from calculations, or an array constant.

Now that you know the basics, let's see how you can use WORKDAY in your Excel spreadsheets.

Afterword

Thank you for reading this article =). I look forward to your feedback and comments below. Downloading the program is absolutely free. I will be glad if this program turns out to be useful for you, I will be happy to answer your questions in the comments. The Timesheet is a humble demonstration of my programming and Excel skills. For more than 10 years I have been professionally involved in programming in the field of automation both in Excel and in the web sphere (writing websites, website parsing, writing bots, simulating user actions, etc.). I’m ready to take on any task, you can contact me through the website or write to me by email

On November 24, 2018, the time sheet in Excel was updated to version 2.0, taking into account all your wishes and comments. Download, use, leave your comments under the article.

How to calculate how many working days are between two dates?

In situations where you need to get the number of working days in a certain time period, use the formula NETWORKDAYS() (in English - NETWORKDAYS):

NETWORKDAYS (start_date; end_date; [holidays])

The first two arguments should already be familiar to you, but the third (optional) argument allows you to exclude a custom list of holidays from the count.

To find out how many weekdays there are between two points in columns A and B, use the following formula:

=NETWORKDAYS(A10,B10)

If you wish, you can enter your list of holidays in a separate range and update the formula to exclude those days:

=NETWORKDAYS(A10,B10,A2:A7)

As a result, only weekdays are taken into account.

What if we have our own special work and rest schedule?

If you need to handle any special non-working days (for example, we rest only on Sunday, on Monday and Sunday, etc.), use the NETWORKDAYS.INTERNAL , which allows you to specify which days of the week are designated for rest.

NETWORKDAYS.INTERNAL ( WORKDAY.INTL in English ) is a more powerful modification of the NETWORKDAYS function, which works with custom weekend parameters . Like its predecessor, it returns the number of working days in the future or past, but allows you to define which days of the week in your schedule should be considered non-working.

The NETWORKDAYS.INTL formula was new in Excel 2010 and is therefore not available in earlier versions of the program.

Its syntax looks like this:

NETWORKDAYS.INTERNAL (start_date, days, [weekends], [holidays])

The first two arguments are mandatory and we have already encountered them several times:

start_date is the starting point.

Days are weekdays before (negative value) or after (positive value) the start point. If this argument is provided as a decimal number as a result of some calculation, it is truncated to an integer.

The last two arguments are optional:

Weekends - indicates which days of the week you do not work. This can be either a number or text as shown below.

The holiday text is a series of numbers of seven zeros and ones representing the seven days of the week, starting with Monday. 1 means a rest day, and 0 means a weekday. For example:

• “0000011” – Saturday and Sunday – closed.
• “1000001” - Monday and Sunday - rest.

At first glance, such designs may seem cumbersome and unnecessary, but I personally like this method better because you can create weekend schedules on the fly without memorizing any numbers.

Holidays is an optional list of days that you want to exclude from your calendar. This can be a range of cells containing dates, or an array constant of sequential values ​​representing them. Everything is the same as before.

And now a few examples.

= NETWORKDAYS.INTERNAL( A10; B10;1) – this is a familiar option when Saturday and Sunday are considered non-working days.

But = NETWORKDAYS ( A10; B10; 11) – rest only on Sunday,

=NETWORKDAYS.INTERNAL( A10; B10;2) – on Sunday and Monday. This calculation is useful if your organization has its own work schedule, not the same as everyone else.

=NETWORKDAYS.INTERNAL(A11,B11,”1100000″,$A$2:$A$7)

We do not work on Monday and Tuesday, the list of holidays is also taken into account.

You can combine the resulting number with your own message by concatenating them using the & operator.

For example, let's look at how much work you still need to do before the end of 2020. To do this, enter December 31, 2020 in date format, and use the following expression:

=""&NETWORKDAYS(TODAY();DATE(2020,12,31),A2:A7)&" days left. until the end of the year!

Wow, only 203 days left! Not as much as I thought Note. To accommodate your individual work schedule, use NETWORKDAYS.INTERNAL().

The schedule is being drawn up in stages.

The accounting period is selected

It is necessary to set the working time recording period - month, quarter, year. The selected time period directly depends on the nature of production, its specifics, and the characteristics of labor.

The time fund for servicing a specific workplace is calculated

For each workplace, the fund of serviced time is calculated. Loading the workplace. The load for any type of schedule is calculated in hours and minutes.

If the employee’s place is occupied for 24 hours (power grid manager). This norm is calculated for the accounting period (year) and is evenly distributed over the working time of individual employees.

The hourly rate is determined

The duration of work of individual specialties has a different meaning from that of the entire staff. The schedule assigns a value to each employee if his standard working time differs from the standard of work of others.

Counting the number of workers

The number of employees per workplace and the total number of staff are calculated. This number of employees must serve the workplace and fulfill the plan. It turns out the quantitative personnel composition - staff. Standard indicator of staffing units of an enterprise.

A schedule is being formed

Job positions are entered into the selected form, then data is filled in for the specialists serving the jobs, and the hourly rate is distributed between them. A production is exhibited that includes all the individual features of each workplace. Weekends and holidays are included. The principle of filling is the principle of uniformity, distribution in approximately equal volumes between specialists.

The number of shifts is calculated and the shifts between employees are recorded.

The main task of creating a schedule is accessibility when perceived by the employee, and simplicity and legibility for calculation by the accounting department. All full-time employees of the enterprise are included in the schedule. The schedule requires daily adjustments (sick leave, business trips, time off). If the basic data is entered in advance, this process is easier than entering data every day.

Weekly and daily timeframe on one chart in Excel

Before making a weekly sales chart in Excel, let's first prepare the incoming data. For example, let’s take sales statistics (in units) for 5 sales agents for the month of March 2020 and place their indicators on a separate “Data” sheet:

The result is a table in the range B2:H33. But to properly compile a weekly sales schedule in Excel, we will need to create another interactive table. Let’s place it on the “Graph” sheet:

The names of the headings of the columns and rows of the table are quite informative and do not require explanation, with the exception of the last column “Bottom of the Line”. It does not contain any formulas (unlike the “TOTAL” column), but only zero values ​​that we will need in the weekly sales chart.

As mentioned above, this sign will be interactive and, when interacting with the user, will change its values ​​according to the condition. The user will indicate the serial number of the week in the month, and the table will be automatically filled with the corresponding values ​​by selecting them from the sales statistics on the “Data” sheet.

Autofill cells when selecting data based on several conditions

Accordingly, the weekly sales schedule will also be dynamic. To interact with the table, the table will use only one control element - a drop-down list. Using it, the report user will be able to indicate the serial number of the week in the month of March. To create a drop-down list, move the Excel cursor to cell G1 and select the tool: “DATA” - “Working with data” - “Data verification”:

In the “Checking input values” dialog box that appears, on the “Parameters” tab, from the “Data type:” drop-down list, select the “List” option. In the “Source:” input field, indicate the order of numbers 1-5 separated by semicolons. Since each month (except for February in a non-leap year) has only 5 incomplete weeks.

Now we need to solve the most difficult problem - sampling values. To automatically fill each cell of an interactive table, you need to make a selection from the table of incoming data on a separate sheet according to 3 conditions at once:

1. You need to get a value from a specific week ordinal number of the month.
2. For a specific sales agent.
3. On a certain day of the week.

Moreover, the task is further complicated by the fact that the two interacting tables are transposed differently. In addition, to make it easier to learn the material from this lesson, I would not like to resort to complex array formulas, but just use a simple and readable VLOOKUP function to select values ​​from a table based on several conditions. After all, all the power is in simplicity!

But there is an elegant solution that is often used when working with databases - this is the generation of a logical ID code for each row of incoming statistical data. To do this, fill in an additional column in the cell range A3:A33 on the “Data” sheet with a special formula:

What is the logic behind the generated ID codes for each line? The logical ID code consists of two-digit numbers:

1. The first number is the week number in the month for each date in the table.
2. The second number is the serial number of the day of the week for each date.

The formula for generating a logical ID code consists of two formulas simultaneously, the return values ​​of which are concatenated with an ampersand sign - “&”:

1. The formula in the first part (before the & symbol) returns the week number of the month for the date: =SELECT(DAY(B3)/7)+1.
2. In the second part, the function: =WEEKDAY(B3;2) with the number 2 in its second argument, thanks to which Monday is indicated as the first day of the week (European format).

Thus, we combine two conditions into one and now to select values ​​from the table of incoming data we only need 2 conditions which we will use in the VLOOKUP function.

Moreover, note that the first column of values ​​in the table you are viewing is already sorted in ascending order, which means there will be no problems when the table is processed by the VLOOKUP function. Overall this is the simplest ideal solution. Simplicity is the key to reliability!

Now we need to fill the range of cells B3:F8 in the interactive table on the “Graph” sheet with a formula:

The formula is based on the VLOOKUP function, and the MATCH function is responsible for working with conditions, which returns the ordinal numbers of days of weeks and columns. The first argument of the VLOOKUP function combines 2 input parameters to form one condition - the serial number of the week in the month and the number of the day of the week (determined by name: Monday-1, Tuesday-2, etc.). And in the third argument of the VLOOKUP function, the MATCH function is used to determine the ordinal number of the column of the incoming data table from which values ​​will be obtained in accordance with the ordinal number of the sales agent's name. Everything ingenious is in the simple!

All incoming data has been prepared and processed, we immediately move on to visualizing it with a weekly sales schedule.



Company budget example Excel

Budgeting in Excel involves creating budget forms in Excel and linking these forms using formulas and macros. The forms of budgets, including the budget of income and expenses, the cash flow budget, can be different, with consolidated items or more detailed, divided into long periods (for example, an annual budget by quarter) or into shorter periods (for example, a monthly budget by week ) – depending on the needs of financial management in the company.

Below is a Budget of Income and Expenses (an example of preparation in Excel) and an example of a Cash Flow Budget.

Figure 1. Budget of income and expenses of an enterprise Excel sample.

Figure 2. Cash flow budget example in Excel.

No obligation

Labor legislation does not contain a direct requirement for employers to draw up a staffing table. However, despite this, it is better to draw up a “staff”. After all, the staffing table contains complete information about the organization’s human resources and the monthly wage fund (Article 57 of the Labor Code of the Russian Federation, section 1 of the instructions, approved by Resolution of the State Statistics Committee of Russia dated January 5, 2004 No. 1). To do this, you need to download the staffing table (form).

Labor relations are an agreement between an employee and an employer (organization or individual entrepreneur) to perform work specified in the contract for payment. During work, the employee is under the management and control of the employer, obeys the internal labor regulations and works in the interests of the employer (Article 15 of the Labor Code of the Russian Federation).

Despite the fact that you need not just a staffing table, but a 2020 form. Having at your disposal a “staffing table”, you can clearly see the structure of the organization and calculate the required amount of money for staff maintenance. Company managers may need this information to make management decisions. This is why organizations need staffing. You can choose a Word or Excel form based on the preferences of the HR manager.

NETWORKDAYS (NETWORKDAYS function)

Note:

We strive to provide you with up-to-date reference materials in your language as quickly as possible. This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Please take a few seconds and let us know if it helped you using the buttons at the bottom of the page. For convenience, we also provide a link to the original (in English).

This article describes the formula syntax and usage of the NETWORKDAYS

in Microsoft Excel.

Returns the number of working days between the start_date and end_date dates. Holidays and weekends are not included in this number. The NETWORKDAYS function can be used to calculate an employee's salary based on the number of days worked in a specified period.

Advice:

To calculate the number of full workdays between two dates using parameters that determine the number of holidays in a week and what days they are, use the NETWORKDAYS.INTERNAL function.

The arguments to the NETWORKDAYS function are described below.

Start_date

Required. Start date.

End_date

Required. Final date.

Holidays

Optional. A list of one or more dates that you want to exclude from your work calendar, such as public holidays. The list can be a range of cells containing dates, or an array constant containing numbers that represent dates.

Important:

Dates must be entered using the DATE function or as the result of other formulas and functions. For example, to specify the date May 23, 2012, use the expression DATE(2012,5,23). Entering dates as text may cause problems.

Microsoft Excel stores dates as sequential numbers so they can be used in calculations. By default, January 1, 1900 is sequence number 1, and January 1, 2012 is sequence number 40909, since it is 40,909 days after January 1, 1900.

If any of the arguments are not a valid date, NETWORKDAYS returns #VALUE! error value.

Copy the sample data from the following table and paste it into cell A1 of a new Excel worksheet. To display formula results, select them and press F2, then press Enter. If necessary, change the width of the columns to see all the data.