Example of date and time subtraction in excel

Subtraction by year, month, and day

1. The datedif function is a hidden function of excel,

DATEDIF (start_date, end_date, unit)
Start_date is a date, which represents the first date or start date in the time period.
End_date is a date, which represents the last date or end date in the time period.
Unit is the type of returned information.
Unit return
The whole number of years in the "Y" period.
The total number of months in the "M" period.
The number of days in the "D" period.
"MD" start_date is different from end_date in Zhongtian. Ignore the month and year in the date.
The difference between "YM" start_date and end_date in the number of months. Ignore the day and year in the date.
"YD" start_date is different from end_date in Zhongtian. Ignore the year in the date.

Prepare an excel file

2. Then, enter = DATEDIF (A1, A2, "y") in the C2 cell, as shown in the following figure:

3. Let's look at another example where we input = DATEDIF (A1, A2, "m") in C4 ")

4, then in C4 input = DATEDIF (A1, A2, "m"), you can also directly input = A2-A1 can also solve the number of days of the difference.

Subtraction by hour and minute

1. Let's look at two examples without using the DATEDIF function. The preparation documents are as follows:

2. We try entering = (A2-A1) * 24 in D2, indicating 5.15 hours as shown below.

3, try again in D3 input = (A2-A1) * 24*60, is on the basis of the hour multiplied by 60 converted to minutes

4. In cell E2 input = TEXT (A2-A1, "[h]: m"), the result is also the number of hours, but each item is different from the above, it indicates 5 hours 15 minutes

5. Input = TEXT (A2-A1, "[m]") in cells E3, and get the same results in minutes and hours.

TEXT function description

The TEXT function converts numeric values to TEXT values and allows you to specify the display format by using special format strings. For example, assume cell A1 contains the number 23.5. To set the number format to RMB, use the following formula:

= TEXT (A1, "￥0.00") Excel displays ￥23.50. If you need to set the numeric format and merge it with other texts = TEXT (A1, "￥0.00 0.235 million") & "" Excel will show $.

Syntax

TEXT (value, format_text)

Parameters

Value is required. A value, a formula with a calculated value, or a reference to a cell containing a value.

Format_text is required. Double quotation marks are used as the numeric format of text strings, for example, "#,## 0.00 ".

Criteria for date and time formats
◆ Use the following code in the format_text parameter to display the day, month, and year.

 

M	Display the month as a number without leading zero.
Mm	Display the month as a number with leading zeros as needed.
Mmm	The abbreviation of month (Jan to Dec ).
Mmmm	Display the month as the complete name of the month (January to December ).
Mmmmm	Abbreviated month to month (J to D ).
D	Display the day as a number without leading zero.
Dd	Display the day as a number with leading zeros as needed.
Ddd	The day is shown as a week (Sun to Sat ).
Aaa	Display day as an abbreviated Chinese lowercase Week (1 ~ Day)
Dddd	Display the day as the full name of the week (Sunday to Saturday ).
Aaaa	Display the day as the full name of the Chinese Week (from Monday to Sunday ).
Y or yy	The year is displayed as two digits.
E or yyyy	Display the year as four digits.

Display Content	Display	Code	Show Chinese	Code
Month	1-12	M	January-December	M month
Month	01-12	Mm	January-December	Mm month
Month	Jan-Dec	Mmm	January-December	[Dbnum1] m month
Month	January-December	Mmmm	? January-December	[Dbnum1] mm month
Month	J-D	Mmmmm	One month-one month	[Dbnum2] mm month
Day	1-31	D	1-31	D Day
Day	01-31	Dd	01-31	Dd day
Day	Sun-Sat	Ddd	I-7	Aaa
Day	Sunday-Saturday	Dddd	Monday-Sunday	Aaaa
Year	00-99	Yy	?? -September	[Dbnum1] yy year
Year	1900-9999	Yyyy	9 ?? -September	[Dbnum1] yyyy year

◆ Use the following code in the format_text parameter to display the hour, minute, and second.

H	Display the hour as a number without leading zero.
[H]	Displays the elapsed time in hours. If the returned hours exceed 24 hours, you can use [H]: mm: ssFormat.
Hh	Display the hour as a number with a leading zero as needed. If the format contains AMOr PMThe hour is displayed in the 12-hour format. Otherwise, the hour is displayed in the 24-hour format.
M	Display the minute as a number without leading zero. Note MOr MmThe code must keep up HOr HhCode followed or followed SsBefore the code.
[M]	The time elapsed is displayed in minutes. If the number of minutes returned exceeds 60, you can use [Mm]: ssFormat.
Mm	Display the minute as a number with a leading zero as needed.
S	Display the second as a number without leading zero.
[S]	The time elapsed is displayed in seconds. If the number of seconds returned exceeds 60, you can use [Ss]Format.
Ss	Display the second as a number with leading zeros as needed. You can use H: mm: ss.00Format.
AM/PM , Am/pm, A/P, morning/afternoon, a/p	Displays hours in 12-hour format. When the time is between midnight and noon, Excel will use AM, Am, A, Morning, AIndicates the time. PM, Pm, P, Afternoon, PTime.