Các Hàm Date Trong Excel / Top 3 # Xem Nhiều Nhất & Mới Nhất 2/2023 # Top View | Hoisinhvienqnam.edu.vn

Các Hàm Ngày Tháng (Date &Amp; Time) Trong Excel

Trong bài này chúng ta sẽ tìm hiểu về các hàm DATE & TIME trong Excel. Excel có sẵn nhiều hàm xử lý thời gian giúp chúng ta có thể đánh dấu dữ liệu theo thời gian một cách nhanh chóng. Tuy vậy cũng có nhiều hàm rất hữu ích mà bạn không biết và ít khi sử dụng, nên trong bài viết tìm hiểu các hàm xử lý Date và Time này mình sẽ tổng hợp lại để giúp bạn dễ dàng tìm hiểu hơn.

1. Hàm Year, Month, Day

Để tách các giá trị year, month, day trong một ngày cụ thể nào đó thì ta thường dùng 3 hàm xử lý là hàm YEAR, hàm MONTH và hàm DAY.

Cú pháp:

YEAR(Serial_number) MONTH(Serial_number) DAY(Serial_number)

Trong đó: Serial_number là con số đại diện cho một ngày cụ thể nào đó.

Vi dụ: Để lấy được năm của một ngày có định dạng mm/dd/yyyy, sử dụng hàm YEAR.

Kết quả trả về ở ô B1 là 2016

2. Hàm Date

Định dạng kết quả trả về của hàm DATE phụ thuộc vào định dạng mà bạn nhập vào. Ví dụ bạn nhập bạn nhập ngày – tháng – năm thì kết quả nó trả về là ngày – tháng – năm. Ngược lại bạn nhập vòa vào tháng – ngày – năm thì nó trả về là tháng – ngày – năm.

Cú pháp:

Để cộng thêm một số ngày vào một ngày có định dạng Ví dụ: mm/dd/yyyy, sử dụng công thức đơn giản như sau.

= CELL có định dạng mm/dd/yyyy + number (số ngày)

Ví du trên ta thấy cho ngày 23 và muốn tìm ngày 28 thì ta cộng thêm 5 vào ngày 23.

Trên là ví dụ cộng thêm ngày. Giả sử bây giờ bạn cần cộng thêm cả năm, tháng và ngày thì sẽ sử dụng hàm DATE.

Ví dụ: Cho ngày 23/6/2016 muốn tìm ngày 9/1/2020 ta sử dụng:

DATE(year(A1)+4, Month(A1)+2, day(A1)+9)

Lưu ý: Hàm DATE bao gồm ba đối số năm, tháng và ngày. Excel sẽ tự nhận biết được rằng tháng 8 ( 6+2=8=August) có 31 ngày nên sẽ tự chuyển sang tháng tiếp theo ( Ví dụ 23 August + 9 days = 1 September).

3. Hàm Current Date & Time

Hàm Current Date & Time là hàm trả về ngày tháng năm và giờ phút giây hiện tại. Giả sử lúc bạn nhập là 15:30:30 thì kết quả nó sẽ trả về là 15:30:30.

Cú pháp:

Ấn vào một ô bất kỳ trong Excel với cú pháp =now() sẽ hiện ra ngày, tháng, năm và thời gian hiện tại lúc đó, như trên hình là 10:43 vàngày 2/23/2017.

4. Hàm Hour, Minute, Second

Các hàm này dùng để lấy phần giờ, phần phút và phần giây của một giá trị thời gian nào đó.

Cú pháp:

HOUR(serial_number) MINUTE(serial_number) SECOND (serial_number)

Trong đó: Serial_number là biểu thức thời gian hoặc một con số chỉ thời gian.

Ví dụ: Cho giá trị của CELL A1 là 6:45:17, hãy lấy phần giờ cảu ô tính này.

Như vậy kết quả sẽ trả về là 6.

5. Hàm Time Function

Muốn thêm số giờ, số phút, số giây vào biểu thức thời gian trong Excel ta sử dụng hàm TIME.

Cú pháp:

TIME(HOUR, MINUTE, SECOND)

Ví dụ: Cho số liệu trong bảng tính là 6:45:17, hãy sử dụng hàm TIME tìm 8:56:27

=Time(hour(A1)+2, minute(A1)+10, second(A1)+70)

Kết quả là 8:56:27, đây cũng là kết quả cần tìm.

Như vậy là mình đã giới thiệu qua cách sử dụng một số hàm DATE & TIME trong Excel, các hàm này được sử dụng rất thường xuyên nên hy vọng bạn hiểu và biết các áp dụng nó vào thực tế.

Cách Sử Dụng Hàm Date Và Cách Kết Hợp Với Các Hàm Khác Trong Excel

Excel không lưu trữ ngày, tháng dưới định dạng ngày. Thay vào đó, Excel lưu trữ ngày như một dãy số và đây là chính là nguyên ngân chính gây nhầm lẫn. Do đó khi cần tính toán ngày trong Excel, DATE là hàm cơ bản nhất. Hàm DATE được sử dụng để tạo thành một ngày từ ba giá trị ngày, tháng, năm riêng biệt. Hãy theo dõi bài viết sau đây để biết cách sử dụng hàm DATE trong Excel.

1. Cấu trúc hàm DATE

Cú pháp hàm: =DATE(year; month; day)

year: đối số bắt buộc, là năm sử dụng khi tạo ngày. Đối số year có thể bao gồm từ một đến bốn chữ số. Theo mặc định, Excel sử dụng hệ thống ngày tháng 1900, tức là ngày đầu tiên là 01/01/1900.

month: đối số bắt buộc, là tháng sử dụng khi tạo ngày. Phải là số nguyên dương hoặc âm.

day: đối số bắt buộc, là ngày sử dụng khi tạo ngày. Phải là số nguyên dương hoặc âm.

Nếu year từ 0 đến 1899 (gồm cả 0 và 1899), hàm sẽ cộng thêm 1900 vào giá trị đó để tính năm.

Nếu year từ 1900 đến 9999 (gồm cả 1990 và 999), hàm sẽ dùng giá trị đó làm năm.

Nếu year nhỏ hơn 0 hoặc lớn hơn 10000, hàm sẽ trả về giá trị lỗi #NUM!.

Nếu month lớn hơn 12, hàm sẽ thêm số tháng lớn hơn vào tháng đầu tiên trong năm tiếp theo.

Nếu month âm, hàm sẽ trừ số tháng ngược lại vào năm trước.

Nếu day lớn hơn số ngày trong tháng đã xác định, hàm sẽ thêm số ngày lớn đó vào tháng tiếp theo.

Nếu day nhỏ hơn 1,hàm sẽ trừ số ngày lớn hơn đó vào tháng trước.

2. Cách sử dụng hàm DATE

a. Sử dụng hàm DATE để gộp ngày, tháng, năm

Ví dụ ta cần ghép các ngày, tháng, năm sau đây vào thành 1:

Áp dụng cấu trúc hàm như trên ta có công thức cho ô D2 như sau: =DATE(C2;B2;A2)

Hoặc ta cũng có thể viết thẳng số ngày, tháng, năm vào hàm như sau: =DATE(20;02;2020)

Sao chép công thức trên cho các ô bên dưới ta thu được kết quả:

Như kết quả thu được ta có thể thấy:

Tại ô D3: do năm tại ô C3 là 82 < 1900 nên hàm đã tự động cộng thêm 1900 vào để thành số năm là 1982

Tại ô D6: do tháng là -4 là số âm nên hàm đã trừ ngược số tháng lại năm trước 2009 là 2008

Tại ô D7: do ngày là 33 lớn hơn số ngày trong tháng 05 nên hàm đã cộng số ngày lớn hơn vào tháng tiếp theo

Tại ô D8: do số ngày là -3 là số âm nên hàm đã trừ ngược số ngày lại tháng trước đó.

b. Sử dụng hàm DATE kết hợp với các hàm DAY, MONTH, YEAR

Ví dụ ta có ngày ban đầu, sau đó cần tính 4 ngày sau ngày đó, 5 tháng sau ngày đó, 3 năm sau ngày đó.

Bằng cách kết hợp với các hàm YEAR, MONTH, DAY. Ta có công thức tính 4 ngày sau ngày ban đầu cho ô B2 như sau:

Copy công thức cho các ô còn lại trong cột B ta thu được kết quả:

=DATE(YEAR(A2);MONTH(A2);DAY(A2)+4)

Tương tự để tính 5 tháng sau ngày ban đầu ta có công thức cho ô C2:

Công thức để tính 3 năm sau ngày ban đầu cho ô D2:

15 Hàm Xử Lý Date Và Time Phổ Biến Trong Excel

Trong Excel, quản lý dữ liệu kèm với từng mốc thời gian cụ thể cho phép chúng ta qui hoạch được số liệu để tổng hợp báo cáo theo tuần, tháng hay quý được dễ dàng, thuận lợi.

15 hàm xử lý Date và Time phổ biến trong Excel

Khi sử dụng các hàm xử lý Date và Time sẽ có 2 định dạng bạn sẽ gặp là:

Định dạng DateTime.

Định dạng theo số.

Ví dụ: Khi sử dụng hàm để lấy ngày hiện tại, nếu bạn định dạng là Date thì sẽ có kết quả dạng 29-08-2018, còn khi sử dụng dạng số sẽ có kết quả là 43341.

1) TODAY

Là hàm trả về giá trị ngày tháng năm hiện tại. Và tự động cập nhật lại mỗi khi có sự thay đổi tại bảng tính.

Cú pháp: =TODAY()

2) NOW

Hàm Now cũng tương tự như hàm Today nhưng sẽ lấy cả giá trị về thời gian (giờ, phút, giây) hiện tại.

Cú pháp: =NOW()

3) DAY

Cú pháp: =DAY(Serial_number)

Serial_number: là một thời gian cụ thể ở định dạng Date hoặc số.

4) DAYS

Days là hàm trả về số ngày giữa 2 giá trị thời gian.

Cú pháp: =DAYS(end_date, start_date)

5) MONTH

Cú pháp: =MONTH(Serial_Number)

6) YEAR

Cú pháp: =YEAR(Serial_Number)

7) DATE

Hàm kết hợp 3 giá trị Day, Month, Year thành một ngày cụ thể.

Cú pháp: =DATE(year; month; day)

Trong đó:

Year: là giá trị năm, trong khoản từ 1900 → 9999.

Month: giá trị tháng, trong khoản từ 1 →12.

Day: giá trị ngày, trong khoảng từ 1 →31.

8) HOUR

Trả về phần giờ của một giá trị thời gian. Kết quả là một số nguyên từ 0 → 23.

Cú pháp: =HOUR(Serial_Number)

9) MINUTE

Trả về phần phút của một giá trị thời gian. Kết quả là một số nguyên từ 0 → 59.

Cú pháp: =MINUTE(Serial_Number)

10) SECOND

Trả về phần giây của một giá trị thời gian. kết quả là một số nguyên từ 0 → 59

Cú pháp: =SECOND(Serial_Number)

11) WEEKNUM

Hàm trả về số thứ tự của tuần trong năm của một ngày cụ thể.

Cú pháp: =WEEKNUM(Serial_Number, [return_type])

Trong đó:

[return_type]: là một số xác định ngày bất đầu của một tuần. Nếu bỏ qua tham số này thì ngày bắt đầu là chủ nhật (return_type=1).

12) WEEKDAY

Cho biết thứ tự của một ngày trong tuần.

Cú pháp: =WEEKDAY(Serial_Number, [return_type])

Trong đó:

[return_type]: là một số xác định ngày bắt đầu của tuần. Nếu bỏ qua tham số này thì ngày bắt đầu là chủ nhật (return_type=1).

13) NETWORKDAYS

Cũng như hàm DAYS nhưng hàm Networkdays có thêm tham số giúp loại bỏ các ngày xác định trong kết quả.

Cú pháp: =NETWORKDAYS(start_date; end_date; [holidays])

Trong đó:

[holidays]: là ngày mà bạn muốn loại bỏ ra khỏi kết quả trong khoảng thời gian start_day và end_date.

14) DAYVALUE

Hàm trả về chuỗi có có định dạng số từ chuỗi văn bản mà Excel có thể hiểu là thời gian.

Cú pháp: =DATEVALUE(date_text)

15) TIMEVALUE

Hàm trả về số thập phân của thời gian.

Cú pháp: =TIMEVALUE(time_text)

Create A Date Sequence In Excel And Auto Fill Date Series

The tutorial shows how you can leverage the new SEQUENCE function to quickly generate a list of dates in Excel and use the AutoFill feature to fill a column with dates, workdays, months or years.

Until recently, there has been just one easy way to generate dates in Excel – the AutoFill feature. The introduction of the new dynamic array SEQUENCE function has made it possible to make a series of dates with a formula too. This tutorial takes an in-depth look at both methods so that you can choose the one that works best for you.

When you need to fill a column with dates in Excel, the fastest way is to use the AutoFill feature.

Filling a column or row with dates that increment by one day is very easy:

Type the initial date in the first cell.

Select the cell with the initial date and drag the fill handle (a small green square at the bottom-right corner) down or to the right.

Excel will immediately generate a series of dates in the same format as the first date that you typed manually.

Fill a column with weekdays, months or years

To create a series of workdays, months or years, do one of the following:

To auto generate a series of days, weekdays, months or years with a specific step, this is what you need to do:

Enter the initial date in the first cell.

In the pop-up menu, choose Series (the last item).

In the Series dialog box, select the Date unit of interest and set the Step value.

In one of the previous tutorials, we looked at how to use the new dynamic array SEQUENCE function to generate a number sequence. Because internally in Excel dates are stored as serial numbers, the function can easily produce a date series too. All you have to do is to correctly configure the arguments as explained in the following examples.

Note. All the formulas discussed here only work in the latest versions of Excel 365 that support

All the formulas discussed here only work in the latest versions of Excel 365 that support dynamic arrays . In pre-dynamic Excel 2019, Excel 2016 and Excel 2013, please use the AutoFill feature as shown in the first part of this tutorial.

To generate a sequence of dates in Excel, set up the following arguments of the SEQUENCE function:

SEQUENCE(rows, [columns], [start], [step])

Rows – the number of rows to fill with dates.

Columns – the number of columns to fill with dates.

Start – the starting date in the format that Excel can understand, like “8/1/2020” or “1-Aug-2020”. To avoid mistakes, you can supply the date by using the DATE function such as DATE(2020, 8, 1).

Step – the increment for each subsequent date in a sequence.

For example, to make a list of 10 dates starting with August 1, 2020 and increasing by 1 day, the formula is:

=SEQUENCE(10, 1, "8/1/2020", 1)

or

=SEQUENCE(10, 1, DATE(2020, 8, 1), 1)

Alternatively, you can input the number of dates (B1), start date (B2) and step (B3) in predefined cells and reference those cells in your formula. Since we are generating a list, the columns number (1) is hardcoded:

=SEQUENCE(B1, 1, B2, B3)

Type the below formula in the topmost cell (A6 in our case), press the Enter key, and the results will spill across the specified number of rows and columns automatically.

Note. With the default General format, the results will appear as serial numbers. To have them displayed correctly, be sure to apply the

With the default General format, the results will appear as serial numbers. To have them displayed correctly, be sure to apply the Date format to all the cells in the spill range

Make a series of workdays in Excel

To get a series of working days only, wrap SEQUENCE in the WORKDAY or chúng tôi function this way:

WORKDAY(start_date -1, SEQUENCE(no_of_days))

As the WORKDAY function adds the number of days specified in the second argument to the start date, we subtract 1 from it to have the start date itself included in the results.

For instance, to generate a sequence of workdays starting on the date in B2, the formula is:

=WORKDAY(B2-1, SEQUENCE(B1))

Where B1 is the sequence size.

Tips and notes:

If a start date is Saturday or Sunday, the series will begin on the next working day.

The Excel WORKDAY function assumes Saturday and Sunday to be weekends. To configure custom weekends and holidays, use the chúng tôi function instead.

Generate a month sequence in Excel

To create a series of dates incremented by one month, you can use this generic formula:

DATE(year, SEQUENCE(12), day)

In this case, you put the target year in the 1st argument and day in the 3rd argument. For the 2nd argument, the SEQUENCE function returns sequential numbers from 1 to 12. Based on the above parameters, the DATE function produces a series of dates like shown in the left part of the screenshot below:

=DATE(2020, SEQUENCE(12), 1)

To display only the month names, set one of the below custom date formats for the spill range:

mmm – short form like Jan, Feb, Mar, etc.

mmmm – full form like January, February, March, etc.

As the result, only the month names will appear in cells, but the underlying values will still be full dates. In both series in the screenshot below, please notice the default right alignment typical for numbers and dates in Excel:

To generate a date sequence that increments by one month and starts with a specific date, use the SEQUENCE function together with EDATE:

EDATE(start_date, SEQUENCE(12, 1, 0))

The EDATE function returns a date that is the specified number of months before or after the start date. And the SEQUENCE function produces an array of 12 numbers (or as many as you specify) to force EDATE to move forward in one-month increments. Please notice that the start argument is set to 0, so that the start date gets included in the results.

With the start date in B1, the formula takes this shape:

=EDATE(B1, SEQUENCE(12, 1, 0))

Note. After completing a formula, please remember to apply an appropriate

After completing a formula, please remember to apply an appropriate date format to the results for them to display correctly.

Create a year sequence in Excel

To make a series of dates incremented by year, use this generic formula:

DATE(SEQUENCE(n, 1, YEAR(start_date)), MONTH(start_date), DAY(start_date))

Where n is the number of dates you want to generate.

In this case, the DATE(year, month, day) function constructs a date in this way:

Year is returned by the SEQUENCE function that is configured to generate an n rows by 1 column array of numbers, starting at the year value from start_date.

Month and day values are pulled directly from the start date.

For example, if you input the start date in B1, the following formula will output a series of 10 dates in one-year increments:

=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1))

After being formatted as dates, the results will look as follows:

Generate a times sequence in Excel

Because times are stored in Excel as decimals numbers representing a fraction of the day, the SEQUENCE function can work with times directly.

Assuming the start time is in B1, you can use one of the following formulas to produce a series of 10 times. The difference is only in the step argument. As there are 24 hours in a day, use 1/24 to increment by an hour, 1/48 to increment by 30 minutes, and so on.

30 minutes apart:

=SEQUENCE(10, 1, B1, 1/48)

1 hour apart:

=SEQUENCE(10, 1, B1, 1/24)

2 hours apart:

=SEQUENCE(10, 1, B1, 1/12)

The screenshot below shows the results:

If you do not want to bother calculating the step manually, you can define it by using the TIME function:

SEQUENCE(rows, columns, start, TIME(hour, minute, second))

For this example, we’ll input all the variables in separate cells like shown in the screenshot below. And then, you can use the below formula to generate a time series with any increment step size you specify in cells E2 (hours), E3 (minutes) and E4 (seconds):

=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4))

How to create a monthly calendar in Excel

In this final example, we’ll be using the SEQUENCE function together with DATEVALUE and WEEKDAY to create a monthly calendar that will update automatically based on the year and month that you specify.

The formula in A5 is as follows:

=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1, 1)

How this formula works:

You use the SEQUENCE function to generate a 6 rows (the max possible number of weeks in a month) by 7 columns (the number of days in a week) array of dates incremented by 1 day. Hence, the rows, columns and step arguments raise no questions.

The trickiest part in the start argument. We cannot start our calendar with the 1st day of the target month because we do not know which day of the week it is. So, we use the following formula to find the first Sunday before the 1st day of the specified month and year:

DATEVALUE("1/"&B2&"/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1

The first DATEVALUE function returns a serial number that, in the internal Excel system, represents the 1st day of the month in B2 and the year in B1. In our case, it’s 44044 corresponding to August 1, 2020. At this point, we have:

44044 - WEEKDAY(DATEVALUE("1/"&B2&"/"&B1)) + 1

The WEEKDAY function returns the day of the week corresponding to the 1st day of the target month as a number from 1 (Sunday) to 7 (Saturday). In our case, it’s 7 because August 1, 2020 is Saturday. And our formula reduces to:

44044 - 7 + 1

44044 – 7 is 4403, which corresponds to Saturday, July 25, 2020. As we need Sunday, we add the +1 correction.

This way, we get a simple formula that outputs an array of serial numbers beginning with 4404:

=SEQUENCE(6, 7, 4404, 1)

Format the results as dates, and you’ll get a calendar shown in the screenshot above. For example, you can use one of the following date formats:

d-mmm-yy to display dates like 1-Aug-20

mmm d to display month and day like Aug 20

d to display only the day

Wait, but we aim to create a monthly calendar. Why do some dates of the previous and next month show up? To hide away those irrelevant dates, set up a conditional formatting rule with the below formula and apply the white font color:

Where A5 is the leftmost cell of your calendar and B2 is the target month.

For the detailed steps, please see How to create a formula-based conditional formatting rule in Excel.

That’s how you can generate a sequence of dates in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Date sequence in Excel – formula examples (.xlsx file)

You may also be interested in