Using the DIFFERENT function, you can calculate the difference between two dates. Put the start date in one cell and the end date in another. Then enter the formula (see one of the formulas below).
Difference in days
In this example, the start date is in cell D9 and the end date is in cell E9. The formula is placed in cell F9. Parameter "d" returns the number of complete days between two dates.
Difference in weeks
In this example, the start date is in cell D13 and the end date is in cell E13. The "d" parameter returns the number of days. But at the end of the formula is the expression /7 . This expression divides the number of days by 7, since there are 7 days in a week. Note that this result must also be in numeric format. Press CTRL+1. Then select Numerical > Number of decimals: 2.
Difference in months
In this example, the start date is in cell D5 and the end date is in cell E5. Parameter "m" in a formula returns the number of full months between two dates.
Difference in years
In this example, the start date is in cell D2 and the end date is in cell E2. Parameter "y" returns the number of complete years between two dates.
Calculating Age in Years, Months, and Days
You can also calculate age or age. The result will look like this: "2 years, 4 months, 5 days".
1. Use the RAZDAT function to calculate the total number of years.
In this example, the start date is in cell D17 and the end date is in cell E17. Parameter "y" in a formula returns the number of complete years between two dates.
2. Use the RAZDAT function with the "ym" parameter to calculate the number of months.
In another cell, place the RAZDAT formula with the parameter "ym". The "ym" parameter returns the number of months remaining after the last full year.
3. Use a different formula to find the days.
Now you need to determine the number of remaining days. We will do this by writing a different formula than the one above. This formula subtracts the first day of the end month (05/01/2016) from the original end date in cell E17 (05/06/2016). The following describes how this is done. First, the DATE function creates a date, 05/01/2016. She does this based on the year and month in cell E17 (number 1 means the first day of the month). The DATE function displays 05/01/2016. We then subtract this date from the original end date in cell E17, i.e. 5/6/2016. "05/06/2016" minus "05/01/2016" equals 5 days.
4. Optional: Combine the three formulas into one.
All three calculations can be placed in one cell, as in this example. You can use ampersands, quotation marks, and text. It takes longer to enter such a formula, but here all the calculations are in one place. Advice. To add a line break to a formula, press Alt+Enter. This will make reading easier. If you can't see the entire formula, press CTRL+SHIFT+U.
Download samples
You can download an example book with all the examples in this article. Follow the discussion or create your own formulas.
Other date and time calculations
Difference between today and another date
As shown above, the DIFFERENT function calculates the difference between the start and end dates. But instead of entering a specific date, you can use the function in the formula TODAY(). In this case, Excel takes the current date of the computer as the date. Keep in mind that it will change when the file is opened again on a different day.
Calculation of the number of working days, including or excluding holidays
You can calculate the number of business days between two dates using the NETWORKDAYS.INTERNATIONAL function. You can also exclude weekends and holidays.
Before the beginning. Decide whether to exclude holidays. If yes, enter the list of holidays in a separate area or on a separate sheet. Put each holiday date in a separate cell. Then highlight those cells, select Formulas > Assign a name. Name the range MyHolidays and press the button OK. Then create a formula by doing the following:
1. Enter start and end dates.
In this example, the start date is in cell D53 and the end date is in cell E53.
2. In another cell, enter a formula like this:
Enter the formula as in the example above. Parameter "1" in the formula designates Saturdays and Sundays as days off and excludes them from the total.
Note. There is no NETWORKDAYS.INTERNATIONAL function in Excel 2007. But there is a NETWORKDAYS function. The function from the example above in Excel 2007 would look like this: =NETWORKDAYS(D53,E53). You do not need to specify 1 because the NETWORKDAYS function treats Saturday and Sunday as holidays by default.
3. Change parameter "1" if necessary.
If your weekend is not on Saturday and Sunday, replace "1" with another number from the IntelliSense list. For example, "2" designates Sundays and Mondays as days off.
If you have Excel 2007, skip this step. The NETWORKDAYS function in Excel 2007 always considers Saturday and Sunday as holidays.
4. Enter a name for the holiday range.
If you named the holiday range as described in the Before You Begin section, enter that name at the end, as shown here. If you don't have holidays, you can leave out the semicolon and leave out MyHolidays. In Excel 2007, the formula from the example above becomes: =NETWORKDAYS(D53;E53;MyHolidays).
Advice: If you don't want to refer to the holiday range name, you can also type the range instead, for example D35:E:39. Or enter each holiday inside the formula. For example, if the holidays were January 1 and 2 2016, enter them as follows: = NETWORKDAYS. Intl(D53,E53,1,("1/1/2016","1/2/2016")). In Excel 2007, it will look like this: =NETWORKDAYS(D53,E53,("1/1/2016","1/2/2016"))
Elapsed Time Calculation
To calculate elapsed time, you need to subtract another from one time value. Put the start time in one cell and the end time in another. Be sure to include the full time value, including hours and minutes. This is described in more detail below.
1. Enter start and end time.
In this example, the start time is in cell D80 and the end time is in cell E80. Be sure to enter the full time value, including hours and minutes.
2. Set the format to hh:mm.
Select both dates and press CTRL+1 (or +1 on a Mac OS computer). Choose format (all formats) > hh:mm if it is not already set.
3. Calculate the difference between the two times.
In another cell, subtract the value of the start time cell from the value of the end time cell.
4. Set the format to hh:mm.
Press CTRL+1 (or +1 on a Mac OS computer). Select (all formats) > hh:mm.
Date and time in Excel are numbers formatted in a special way. The date is the integer part of the number, and the time (hours and minutes) is the fractional part.
By default, the number 1 corresponds to the date January 01, 1900. That is, each date is the number of days that have passed since 01/01/1900. In this lesson, we will look at dates in detail, and in the following lessons we will look at time.
How to count days between dates in Excel?
Since the date is a number, it means that it is possible to carry out mathematical computational and settlement operations with it. Calculating the number of days between two Excel dates is not a problem. For an illustrative example, we will first perform the addition, and then the subtraction of dates. For this:
- On a blank sheet in cell A1, enter the current date by pressing CTRL+;.
- In cell A2, enter the interim period in days, such as 127.
- In cell A3, enter the formula:=A1+A2.
Please note that the "Date" format was automatically assigned to cell A3. It is not difficult to guess that in order to calculate the difference in dates in Excel, you need to subtract the oldest date from the newest date. In cell B1, enter the formula:=A3-A1. Accordingly, we get the number of days between these two dates.
Calculate age by date of birth in Excel
Now we will learn how to calculate age by date of birth:
- On a new sheet in cells A1:A3, enter the dates: 04/18/1985; 08/17/1977; 12/08/1992
- Enter the current date in cells B1:B3.
- Now you need to use the function to convert the number of days to the number of years. To do this, manually enter the following value in the range C1:C3: =DIFFERENT(A1;B1;"y").
Thus, the application of the function allowed us to accurately calculate the age by date of birth in Excel.
Attention! To convert days to years, the formula is not enough: \u003d (B1-A1) / 365. Moreover, even if we know that 1 day = 0.0027397260273973 years, then the formula: = (B1-A1) * 0.0027397260273973 will also not give an accurate result.
Days into years are most accurately converted by the function: = RAZDAT(). English version =DATEDIF(). You won't find it in the Function Wizard list (SHIFT+F3). But if you just type it into the formula bar, it will work.
The RAZDAT function supports several parameters:
Let's illustrate an example of using several parameters:
Attention! For the function:=RAZDAT() to work without errors, make sure that the start date is older than the end date.
Entering a Date in an Excel Cell
The purpose of this lesson is an example of mathematical operations with dates. Also, we will make sure that for Excel the date data type is a number.
Fill in the table with dates as shown in the picture:
Various ways to enter dates. Column A is the input method, and column B is the display result.
Note that in the default cell format "General", dates as well as numbers are right-aligned and text is left-aligned. The value in cell B4 is recognized by the program as text.
In cell B7, Excel itself assigned the current year (now 2015) by default. This can be seen when displaying the contents of the cells in the formula bar. Notice how the value was originally entered in A7.
Excel Date Calculation
On a blank sheet in cells A1:B1 enter 01/01/1900, and in cells A2:B2 enter 01/01/1901. Now we change the cell format to "numeric" in the selected range B1:B2. To do this, you can press the key combination CTRL+SHIFT+1.
B1 now contains the number 1, and B2 - 367. That is, one leap year (366 days) and 1 day has passed.
You can set how the date is displayed using the Format Cells dialog box. To call it press: CTRL+1. On the "Number" tab, select "Number formats" - "Date" from the list. The Type section displays the most popular formats for displaying dates.
Apparently this is a very common occurrence. Immediately got on the Internet to look for an online service that allows calculate how old a person is by his date of birth.
As expected, such services exist. And not a few. Talented PHP programmers took care of "lost in time" and wrote all kinds of calculator scripts to calculate years by date of birth, or just calculate the number of years, months, days, hours, minutes and even seconds between two dates online.
I would like to write about some of them.
Calculation of how many years, months, days, hours, minutes have passed by date of birth
Many of you are probably wondering: "And how many days do I live?" . But not many bothered to count. It's a very tedious task. But with the help of this site:
can easily calculate by date of birth. You will find out how old you are, months, days, hours, minutes, seconds and even milliseconds.
The service will also give you what time your next birthday will be.
Calculate days and years between two dates - online calculator
http://planetcalc.ru/274/ - with the help of this calculator you will be able to count days and years between two dates in online mode. Just enter the required data and the service will give you the exact result.
Counting the number of days from a date to a date
http://www.calculator888.ru/skolko-dnei/ is another site where you can find out exactly how old a person is. Moreover, it can calculate online the number of years, months, days, hours, minutes, seconds elapsed from one date to another.
Calculations of time are constantly encountered in everyday life: from calculating the days before a significant date to calculating the time of a vacation or the period of payments on a bank loan. A collection of online calculators will help you easily operate with such a complex parameter as time.
Time
Time management is not the name of a magic spell from a computer game, but a very real ability that has gained immense popularity among businessmen and investors. Time management or is a technique for calculating time intervals for the effective implementation of a certain amount of work. Thanks to competent and rest periods, people using the money management technique manage to do much more than those who do not keep track of time and suffer from.
Naturally, time management is a science not only about the distribution of time. The most important skills that allow you to competently organize work include:
- and manage resources;
- prioritize and ;
- allocate time and analyze results.
Delegation is the transfer of work to subordinates or colleagues. Many ineffective leaders believe that no one can do better than themselves. Naturally, they are overwhelmed with a bunch of insignificant work, they do not have time to complete priority tasks, as a result of which they become inefficient.
Finding priorities is just as important. says that 80% of the result comes from 20% of the effort. In practice, this means that it is important to complete on time only those tasks on which 80% of success depends. As a rule, there are few such tasks, not strictly 20% as promised by the Pareto principle, but usually in the range from 20 to 40%. It is the ability to separate the wheat from the chaff that creates productive leaders and businessmen.
The most famous, effective and at the same time the simplest technique is Pomodoro. This is a time management technique, according to which work is performed at strictly allotted time intervals (usually 20 minutes), each of which is followed by a five-minute rest. The Pomodoro technique got its name because its creator measured time intervals using a kitchen timer in the form of a tomato. Since then, modernized versions of time management have been at the heart of the success of prominent business representatives.
Timing
You can use the principles of money management not only when solving daily tasks, but also when planning large projects, the implementation of which takes weeks or months. First of all, you should find out by what date the project needs to be handed over or how much time is allocated for it. Let's consider in more detail.
Number of days between two dates
This tool allows you to determine the number of days that fit between two dates. For example, on October 20, 2017, you were assigned a project that needs to be completed by January 18, 2018. Going to the calendar and counting the time is not very convenient and it is easier to use a calculator: just select the type of program and enter both dates. In the answer, we see that you have 2 months and 29 days to complete the plan. Not too informative when planning. The program expresses this time also in days, weeks or months. We look. You have exactly 90 days or 12 working weeks. With this, you can already build an effective time management system and avoid deadlines.
What date will be in n days
Another handy tool for efficient work. A large project at work may be assigned with the mark "complete within 50 days after the order is accepted." This is a lot of time, but running back to the calendar and calculating it is not very convenient. We use a calculator. Suppose an order was accepted for work on April 28, 2017. By what date does it need to be handed over to the customer? Let's change the calculator type and calculate the deadline date. It will be 17 June 2017, Saturday. Having at hand the total number of days and date x, you can easily distribute forces for timely completion of work.
What date was n days ago
This calculator will not be useful to you at work, but will certainly come to the rescue in your personal life. Imagine that you received an SMS message in which your passion congratulates you on your 100th day of marriage. This is an important date that should not be forgotten, so it is better to use the program and find out. You received a text message on July 4, 2017, now it's easy to find out when you moved in with your passion. So, select the type of calculator, enter the date and the anniversary 100 days. Your memorable date is March 26, 2017, Sunday. Circle this date on your calendar.
Temporary values
This calculator allows you to convert one time value to another. The program can be used to convert minutes to days, weeks to years, or centuries to millennia. In practice, this can come in handy when calculating working hours for freelancers and freelance artists. For example, you have 28 business days to complete your next order. This is 672 hours. Subtract the time for sleep 28 × 8 = 224, the time for breaks and rest 28 × 4 = 112 and we get that you have 336 hours to work effectively. You can already work with this and use time management techniques for productive work.
Sum/time difference
This program gives you the ability to add hours or days and calculate the total time in months, weeks, days, minutes, seconds and even milliseconds. This is an entertaining calculator that can be used in practice to calculate the time required to complete several types of work or to calculate the free time left after the completion of the work.
If on an Excel sheet you work not only with numbers, graphs, pictures, but also with dates, then you probably came across a situation where you had to calculate the difference between them. For example, you need to determine the number of days or months for a certain period of time, or calculate the age of a person so that the result is in days, months and years, or maybe it is required to calculate working days.
There is already an article about calculating the difference in Excel on the site, and in it I touched on dates a little. But now let's look at this issue in more detail and figure out how to calculate the number of days between two dates in a simple way or using the RAZDAT () function, and how to determine the number of working days.
Method 1: subtraction
Let's start with the simplest - we subtract the second from one date, and we get the value we need. Before that, make sure that the format of the cells in which the numbers are entered is selected "Date".
If you haven’t filled them in yet, then select the range you want to work with and click on the small arrow next to the name of the “Number” group.
In the window that opens on the left, select the format that suits us, and then in the main area decide on the type: 03/14/12, 14 Mar 12 or another. Press "OK".
In those cells for which you have just changed the format, enter data. I filled out A1 and B1. Now you need to select any cell ( D1 ) in which the general data format is set, otherwise the calculations will be incorrect. Put “=” in it and press first the latest (B1) date, then the early (A1). To calculate the number of days between them, press "Enter".
Method 2: using a function
To do this, select the cell in which the result will be (B3), and see that the general format is selected for it.
To calculate the days, we will use the RAZDAT() function. It includes three arguments: start and end date, unit. The unit is what we want to get the result in. Substituted here:
"d" - the number of days;
"m" - the number of full months;
"y" - the number of full years;
"md" - will count the days without taking into account months and years;
"yd" - counting days without taking into account only years;
"ym" - counts the months without taking into account the year.
We put an equal sign in B3, write RAZDAT and open the bracket. Then we select the early date (A1), then the late one (B1), put the appropriate unit in quotation marks and close the bracket. Between all arguments put ";" . To calculate, press "Enter".
I got this formula:
RAZNDAT(A1;B1;"d")
Choosing “d” as the unit, I got the result - 111.
If you change this value, for example, to "md", then the formula will calculate the difference between 5 and 24, excluding months and years.
By changing this argument in this way, it will be possible to derive the exact age of a person. In one cell there will be years "y", the second months "ym", the third days "md"
Method 3: counting working days
Let's take this table as an example. In column A we have the beginning of the month or the starting date of the count, in B we have the end of the month or the count. This function counts working days, excluding Saturdays and Sundays, but there are also holidays in months, so column C will be filled in with the corresponding dates.
NETWORKDAYS(A5;B5;C5)
As arguments, we specify the start date (A5), then the end date (B5). The last argument is the holidays (C5). We separate them ";" .
By pressing "Enter" the result will appear, in the example cell D5 - 21 days.
Now consider if there are several holidays in a month. For example, in January New Year and Christmas. Select the cell ( D6 ) and put it equal. Then click on the letter "f" in the formula bar. A window will open "Insert Function". In the "Category" field, select "Full alphabetical list" and find the desired function in the list. Click OK.
Next, you need to select the arguments of the function. In "Start_date" we select the initial value (A6), in "End_date" - the final (B6). In the last field, enter the dates of the holidays in brackets () and quotes "". Then press "OK".
As a result, we will get the following function and the value will be calculated excluding weekends and specified holidays:
NETWORKDAYS(A6,B6,("01/01/17", "01/07/17"))
In order not to register holidays manually, you can specify a certain range in the corresponding field. I have it C6:C7.
Working days will be counted, and the function will look like:
NETWORKDAYS(A6;B6;C6:C7)
Now let's do the calculation for the last month. Enter the function and fill in its arguments:
NETWORKDAYS(A8;B8;C8)
In February, it turned out 19 working days.
About other date and time functions in Excel, I wrote a separate article, and you can read it by clicking on the link.
