excel date subtraction company report is out of day, month, year, but after subtraction, it is alway

The function is wrong, and here's how to do it:

The function is a hidden function of Excel, which is mainly used to calculate the difference between dates, not rounding, but rounding down.

2.As an example, enter =datedif(A1,A2,) in cell C2"y"), where "y" is the difference in the calculation years.

3.Also in c4 input =datedif(a1,a2,"m"), where "m" is the difference between the months of calculation.

4.Also in c4 input =datedif(a1,a2,"m"), where "m" is the difference in the number of days to be calculated. You can also directly enter =a2-a1 to solve the difference in days.

5.In the following example, the difference between hours and minutes is found in two ways.

6.Enter = (a2-a1)*24 in d2 to indicate the hour.

7.In D3, input = (A2-A1)*24*60, which is converted into minutes by multiplying 60 on the basis of hours.

8.In cell E2 enter =text(a2-a1,"[h]:m"), the result is also the number of hour intervals, but the items are different from the above, which represents 5 hours and 15 minutes.

9.Enter =text(a2-a1,) in cell e3"[m]"), the number of minutes is obtained, and the results of the hour counting method are consistent.

B1-A1 can be written as:

date(mid(b1,7,4),mid(b1,4,2),left(b1,2))-date(mid(a1,7,4),mid(a1,4,2),left(a1,2))

It can be displayed in numeric format.

Subtraction is possible, but first of all, the format of these two columns must be set to date, in addition, if the date is popular in China, such as 2008-01-23 or something, 27 06 2011, this may not be supported. But I didn't install Excel, I tried it in WPS**, maybe which version of Excel will do.

Make use of the auxiliary column c and write the formula as follows:

Then format this column as a date.

Ibid. in column D. Be sure to set it to date format.

Then f=d1-c1 comes out.

I can't do it yet, and I leave QQ

I don't know which version you use.

The correct format for entering the date is: 2011-6-27 2011-3-7 14:00

If you press the correct input method, column C can be used as a formula:

a2=2011-6-27

b2=2011-3-7 14:00

Then the formula C2 is as follows:

text(b2,"yyyy-mm-dd")-(text(a2,"yyyy-mm-dd"))

a b c

Method: Set the format of a:b cell to dd mm yyyy, manually fill in 6-27 to a1, 7-3 to b1, c1 input formula =b1-a1, you can get the result.

Note: When filling in the data in columns A and B, it cannot be dd mm yyyy, and it must also be filled in the date standard method.

If the cell format is not set, the formula for c1 is: =date(mid(b1,7,4),mid(b1,4,2),left(b1,2))-date(mid(a1,7,4),mid(a1,4,2),left(a1,2)).

In fx=column a-column b

The format is custom, and the type is "dd".

Can this format be reduced? If you can, it's too smart.

1. The Datidi function is a hidden function in Excel, which is mainly used to calculate the difference of dates, not the difference of rounding and rounding.

2. The following figure is an example, in the C2 unit input = datidif (A1, A2,"y"), where"y"Represents the difference in the calculation year.

3. Also in C4 input = datiif (A 1, A2,"m"), where"m"Represents the difference in the calculation month.

4. Also in C4 input = datidi (A 1, A2,"m"), where"m"Represents the difference in the number of days calculated. You can also directly enter = a2-a1 to resolve the difference in days. You can subtract the date in the excel sheet from the month.

1. The software used in the demonstration is the Excel office software under the Office series, and the demo version is Office Home and Student Edition 2016.

2. Open the office software excel, and then enter the data required to subtract the date to find the month in the excel electronic **.

3. Select cell C2 and enter the formula "=datedif(A2,B2,"m"", which indicates the difference between the date between cell A2 and cell B2.

4. Click Enter to get the result, and you can see that the result is correct.

5. In order to test whether the function used is completely correct, we are conducting a numerical demonstration, as shown in the figure below, you can see that the months of difference have been manually calculated, and the result of the calculation is the same.

The date can be directly subtracted from the number to get the date, the two dates are subtracted to get the number of days apart, and the month can be found with month().

a1 and b1 are two dates, b1 is greater than a1, enter the following formula in c1 to calculate the number of months between the two dates:

datedif(a1,b1,"m")

=datedif(small date, big date,"m"The result is an integer ...... of the number of months for the big date-small dateThe parameter m is the number of months.

y is the number of years. d is the number of days.

The date with time is entered in the form of "year, month, day, time", such as "2014 91 5 12:00", and you can customize the format you want to display after entering, and if you directly enter it like that, it will be used as text.

Because your date and time are not in the standard date and time format, there are spaces in between, and if the spaces are entered manually, these two values are text, and subtracting text has no result.

Just convert it to a standard datetime format to add and subtract (to show this effect, you can do this by customizing the cell format).

Change d1 to read: 2014 10 9 12:00:00

Change e1 to: 2014 10 16 09:56:05 and subtract.

This error message is generated when the wrong parameter or operator type is used, or when the auto-change formula function fails to correct the formula (as value! ）。

Workaround: Confirm that the parameters or operators required by the formula or function are correct, and that the cells referenced by the formula contain valid values.

Subtraction is used between numeric formats, not date formats.

It needs to be converted into a standard representation of time and then calculated.

No, dates can be subtracted to appear negative, why are you wrong?

Oh, I see. Don't use datedif, its first argument must be less than the second argument. Just subtract it directly.

Enter the following formula in cell E1, and then fill the formula down.

datedif(a1,b1,"d")

Select column E, click Menu - Format - Cell Format, set the format to "Numeric", and then select the negative form you need in the "Negative Number" option on the right, and confirm.

=b1-a$1 to format the cell to the regular format, and then drop down.

1. After opening the file with the 2007 version of EXEL, the dates in B14 and A14** should be subtracted.

2. Assuming that the difference in years is required, enter the formula in C1**: =DateDif(A14,B14,"y")。

3. Then press enter, and you will get the difference of the year.

4. Assuming that the number of months difference is required, enter the formula in C1**: =DateDif(A14,B14,"m")。

5. Then press enter again to get the difference in months.

6. Assuming that the difference in days is required, enter the formula in C1**: =DateDif(A14,B14,"d")。

7. Then press enter, and you can get the number of days between the two dates.

You can use the datedif function to calculate the difference in years.

Software version: Office 2013

Here's how:1Calculate the difference between the following two dates:

2.Calculate the difference month formula:

3.Calculate the difference year formula:

4.Calculation results:

For example, cell A1 is:"2012/3/12", cell b1 is"2012/10/21", cell C1 takes the mammothic input formula.

text(b1-a1,"0"To subtract the smaller date from the larger date, the value is 223 days.

The date format should be changed to the default date format of excel, and the following formula will be tried.

text(datedif(a2,b2,"y"),00"Filial piety.

Use the datadif function.

For example, the date of A1 is late (2014), B1 is the year of Shen Sanis (2001), and the formula for seeking the letter and filial piety is .

datedif(b1,a1,"y"Slip vertical).

Monthly difference. datedif(b1,a1,"m")

In Excel, if the data in the cell is in "Date" format, the cell format is displayed as "General" as shown in the following figure:

That is, usually the value of the date is a number, and the display format can be set by yourself, such as 2017-1-23 or 2017-01-23 and so on.

As you can see from the screenshot of the problem, the regular format of the cell is not a numeric value, but a "string" from the display.

Check that there should be spaces or invisible characters in these two date cells.

Excel dates are subtracted to get the number of months, and we usually use the Datedif function to get the months between two dates.

For example, the following excel example: how to subtract two dates to get the month, the date subtraction needs to take into account the date situation of the year.

The method of obtaining the number of months subtracted from the date in Excel uses the Datedif function.

The basic syntax of the datedif function to subtract the dates to get the number of months: =datedif(start date, end date, "m"), where the parameter "m" indicates the number of months (not rounded) by the difference between the dates.

The first parameter represents the start time, the second parameter represents the end time, and the third parameter represents the returned result, which can be "y"."m" "d"It should be noted that the result of the calculation is the number of whole years or whole months between these two dates.

Calculate the difference between two dates in Excel and get the number of months, which can be achieved with the Datedif function.

The formula is: =datedif(a1,b1,"m")。The first parameter of the function is the start date, the second parameter is the end date, and the third parameter defines whether the return value is the number of years, months, or days.

The number of years is returned with the parameter "y", the number of months is "m", and the number of days is "d".

It's good to propose the number of months first and then subtract it.

For example, =month(a1)-month(b1).