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.
|