Example of date and time subtraction in excel

Source: Internet
Author: User
Tags numeric

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.