Cập nhật nội dung chi tiết về Create A Date Sequence In Excel And Auto Fill Date Series mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
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
Excel Filter For Date Range 2 Ways – Contextures Blog
In Excel, you can use the drop down arrows in the table headings, to sort or filter the data. In this example, we’ll filter for dates that fall within a specific date range. The video shows two ways to accomplish that task.
Use a Date Filter
The first way to filter for a date range, is to use one of the Date Filter options. There are dynamic date range options, such as This Week, or Last Year.
Use Conditional Formatting
The second option that I used in the video was a Filter by Font Color. Thanks to UniMord for suggesting this technique!
First, I created a temporary worksheet formula, using the AND function. It compared the date in cell B4 to the start date, and to the end date.
Once the worksheet formula was working correctly, I copied it, using Ctrl+C.
Add Conditional Formatting
Next, I selected the Date column in the table, and added conditional formatting to all the date cells.
For the Formula, I used Ctrl+V to paste in the worksheet formula that I had copied in the previous step.
For the formatting, on the Font tab, I selected Black – Text 1 – 5% lighter.
Excel will detect the difference, but on the worksheet, the dates should all look the same. That way, if you want to print the sheet, you won’t need to worry about the font colour.
Filter by Colour
The table is filtered to show only the rows in the selected date range.
And it’s easy to change the date range. Just type new dates on the worksheet, then apply the Filter by Color again.
NOTE: You can clear the cells where the worksheet formulas were entered. They were just used for testing.
Get the Workbook
To download the AutoFilter for Date Range workbook, go to the AutoFilter page on my Contextures website. The zipped file is in xlsx format, and does not contain macros.
And you can find more Conditional Formatting examples on my Contextures site too!
Video: Filter for Date Range
Here’s the video that shows the steps for two ways to filter for a date range in Excel. Use a Date filter, or use Filter by Color, after applying Conditional Formatting. The timeline is below the video
Video Timeline:
0:00 Intro
0:17 Use the Date Filter
2:01 Another Way to Filter
2:42 Create a Formula
4:03 Add Conditional Formatting
6:21 Get the Workbook
Excel Filter for Date Range 2 Ways
Hướng Dẫn Cách Dùng Hàm Date Trong Excel
Khi tính toán ngày trong Excel, DATE là hàm cơ bản nhất. Như bạn có thể biết, Excel không lưu trữ ngày, tháng dưới định dạng ngày. Thay vào đó, Microsoft 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.
Những gì hàm DATE làm là trả về kết quả số cho một ngày cụ thể. Nó có công thức sau:
Year – chỉ năm. Excel diễn giải đối số năm theo hệ thống ngày được thiết lập trên máy tính của bạn. Theo mặc định, Microsoft Excel cho Windows sử dụng hệ thống 1900. Trong hệ thống ngày tháng này:
Nếu năm trong khoảng 1900 và 9999, chính giá trị đó được sử dụng trong năm. Ví dụ: = DATE (2015, 12, 31) trả về ngày 31 tháng 12 năm 2015.
Nếu năm trong khoảng giữa 0 và 1899, Excel tính năm bằng cách cộng thêm số được chỉ định vào năm 1900. Ví dụ: = DATE (100, 12, 31) trả về ngày 31 tháng 12 năm 2000 (1900 + 100).
Nếu năm nhỏ hơn 0 hoặc lớn hơn 9999, hàm DATE sẽ trả về #NUM! lỗi.
Mẹo. Để tránh nhầm lẫn, luôn luôn ghi bốn chữ số của năm. Ví dụ: nếu bạn nhập “01” hoặc “1” vào đối số năm, hàm DATE sẽ trả về kết quả năm 1901.
Month – một số nguyên đại diện cho tháng trong năm, từ 1 (January) đến 12 (December).
Nếu tháng lớn hơn 12, Excel sẽ cộng số đó vào tháng 1 của năm được chỉ định. Ví dụ: = DATE (2015, 15, 5) trả về kết quả ngày 1 tháng 3 năm 2016 (ngày 5 tháng 1 năm 2015 cộng với 15 tháng).
Nếu tháng nhỏ hơn 1 (số không hoặc giá trị âm), Excel trừ đi số tháng đó và trừ thêm 1, kể từ tháng đầu tiên của năm được chỉ định. Ví dụ: = DATE (2015, -5, 1) trả lại ngày 1 tháng 7 năm 2014 (ngày 1 tháng 1 năm 2015 trừ đi 6 tháng).
Day – một số nguyên tương ứng với ngày trong tháng, từ 1 đến 31.
Cũng như tháng, đối số ngày có thể được cung cấp như một số dương và âm, và Excel tính giá trị của nó dựa trên các nguyên tắc tương tự như mô tả ở trên.
Mẹo. Lúc đầu, việc cung cấp giá trị âm trong đối số tháng hoặc ngày của hàm DATE có vẻ như vô lý, nhưng trên thực tế nó có thể trở nên khá hữu ích, ví dụ trong công thức phức tạp chuyển đổi tuần.
Hàm DATE có sẵn trong tất cả các phiên bản của Excel 2016, Excel 2013, Excel 2010, Excel 2007, 2003, XP, 2000 cũng như trong Excel 2016.
Hãy bắt đầu từ những cái đơn giản nhất.
Đây là cách sử dụng rõ ràng nhất của hàm DATE trong Excel:
= DATE (2015, 5, 20) – trả về kết quả ngày 20 tháng 5 năm 2015.
Thay vì chỉ định các giá trị đại diện cho năm, tháng và ngày trực tiếp trong một công thức, bạn có thể nhận được một số hoặc tất cả các đối số như là kết quả của các hàmkhác:
=DATE(YEAR(TODAY()), 1, 1) – cho kết quả ngày đầu tiên của năm hiện tại.
=DATE(YEAR(TODAY()), MONTH(TODAY(), 1) – cho kết quả ngày đầu tiên của tháng hiện tại trong năm hiện tại.
= DATE (A2, A3, A4) – trả về kết quả ngày, tháng , năm từ ô A2, A3 và A4.
Thêm ngày vào một ngày:
= DATE (2015, 5, 20) + 15
Công thức cộng thêm 15 ngày cho ngày 20 tháng 5 năm 2015 và trả về kết quả ngày 4 tháng 6 năm 2015.
Trừ ngày kể từ ngày:
= DATE (2015, 5, 20) – 15
Kết quả của công thức trên là ngày 5 tháng 5 năm 2015, tức là ngày 20 tháng 5 năm 2015 trừ đi 15 ngày.
Để trừ một ngày từ ngày hôm nay:
= TODAY () – NGÀY (2015,5,20)
Công thức tính số ngày nằm giữa ngày hiện tại và một ngày khác mà bạn chỉ định.
Sử dụng hàm DATE trong định dạng có điều kiện
Trong trường hợp bạn không chỉ tính toán mà còn đánh dấu ngày tháng trong bảng tính Excel, sau đó tạo các quy tắc định dạng có điều kiện dựa trên hàm DATE.
Giả sử bạn có một danh sách các ngày trong cột A và bạn muốn tô màu cam ngày trước ngày 1 tháng 5 năm 2015 và xanh lá cây những ngày xảy ra sau ngày 31 tháng 5 năm 2015.
Công thức DATE bạn muốn là như sau:
Màu cam: = $A2 <NGÀY (2015, 5, 1) – tô màu ngày trước 1 tháng 5 năm 2015
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel cơ bản đến chuyên gia dành cho người đi làm
Hàm Date Trong Excel, Cách Sử Dụng, Cấu Trúc Hàm
Định nghĩa hàm Date, hàm ngày tháng trong Excel
Hàm DATE được tích hợp sẵn trên Excel cho phép người dùng tạo một ngày với đầy đủ các thành phần năm, tháng và ngày. Đặc biệt hàm DATE cực kỳ hữu ích trong trường hợp cung cấp ngày làm đầu vào cho các hàm khác như hàm SUMIFS hay hàm COUNTIFS, vì người dùng có thể dễ dàng tập hợp các giá trị năm, tháng và ngày từ một ô tham chiếu hoặc kết quả công thức.
Mục đích
Tạo các ngày hợp lệ từ năm, tháng và ngày.
Giá trị trả về
Trả về kết quả là giá trị số của một ngày cụ thể trong Excel.
Cú pháp
= DATE (năm, tháng, ngày)
Các tham số
Năm – Năm sử dụng khi tạo ngày.
Tháng – Tháng để sử dụng khi tạo ngày.
Ngày – Ngày để sử dụng khi tạo ngày.
Lưu ý
– Hàm DATE trong Excel trả về kết quả là giá trị số của một ngày cụ thể. Định dạng kết quả một ngày để hiển thị là một ngày (gồm năm, tháng, ngày).
– Nếu năm nằm trong khoảng giữa 0 và 1900, Excel sẽ thêm 1900 vào năm.
– Tháng có thể lớn hơn 12 và nhỏ hơn 0. Nếu tháng lớn hơn 12, Excel sẽ thêm tháng vào tháng đầu tiên trong năm. Nếu tháng nhỏ hơn hoặc bằng 0, Excel sẽ trừ đi giá trị tuyệt đối của tháng cộng thêm 1 (ví dụ ABS (tháng) + 1) tính từ tháng đầu tiên của năm.
– Ngày có thể tương đối hoặc tuyệt đối. Nếu ngày lớn hơn các ngày trong tháng cụ thể, Excel sẽ thêm ngày vào ngày đầu tiên của tháng được chỉ định. Nếu ngày nhỏ hơn hoặc bằng 0, Excel sẽ trừ giá trị tuyệt đối của ngày và cộng thêm 1 (ví dụ ABS (ngày) + 1) tính từ ngày đầu tiên của tháng được chỉ định.
Giải thích:
Nếu muốn đánh dấu các ngày lớn hơn hoặc nhỏ hơn một ngày cụ thể với định dạng có điều kiện, bạn có thể sử dụng công thức đơn giản dựa trên hàm DATE. Ví dụ, nếu bạn có ngày trong các ô B4: G11 và làm nổi bật các ô chứa ngày lớn hơn ngày 1/8/2015, chọn phạm vi và tạo quy tắc CF mới sử dụng công thức này:
Sau khi lưu rule, bạn sẽ nhìn thấy các ngày lớn hơn ngày 8/1/2015 được đánh dấu.
Ý nghĩa công thức:
Hàm DATE tạo một ngày hợp lệ trên Excel với các giá trị năm, tháng, và ngày. Sau đó, chỉ đơn giản là vấn đề so sánh mỗi ngày trong phạm vi với ngày được tạo bằng hàm DATE. Tham chiếu B4 hoàn toàn tương đối, vì vậy sẽ cập nhật theo rule được áp dụng cho mỗi ô trong vùng và bất kỳ ngày nào lớn hơn 8/1/2015 sẽ được đánh dấu.
So sánh lớn hơn hoặc bằng:
Bạn có thể sử dụng tất cả các toán tử tiêu chuẩn trong công thức này để điều chỉnh hành vi nếu cần. Ví dụ, để làm nổi bật tất cả các ngày lớn hơn hoặc bằng ngày 8/1/2015, sử dụng công thức:
Sử dụng một ô khác để làm đầu vào:
Không cần mã hóa ngày tháng vào rule. Để tạo ra một rule tương tác linh hoạt hơn, sử dụng một ô khác làm biến trong công thức. Ví dụ, nếu muốn sử dụng ô C2 làm ô đầu vào, đặt tên cho ô C2 “input”, nhập ngày và sử dụng công thức này:
Sau đó thay đổi ngày trong ô C2 thành bất kỳ thứ gì bạn muốn và rule định dạng có điều kiện sẽ phản hồi ngay lập tức.
– Ví dụ 2: Lấy ngày cuối cùng của tháng Công thức chung:
=EOMONTH(date,0)
Giải thích:
Cách dễ nhất để tính ngày cuối cùng của tháng là sử dụng hàm EOMONTH.
Trong ví dụ trên, công thức trong ô B5 là:
=EOMONTH(B5,0)
Ý nghĩa công thức:
Đối số thứ 2 (tháng) của hàm EOMONTH cho phép bạn lấy được ngày cuối cùng trong những tháng tiếp theo hoặc trong tháng trước. Khi bạn sử dụng số 0 trong nhiều tháng, EOMONTH sẽ trả lại kết quả vào ngày cuối cùng trong tháng đó.
Để lấy ngày cuối cùng của tháng trước, sử dụng công thức:
=EOMONTH(date,-1)
Để lấy ngày cuối cùng của tháng tiếp theo, sử dụng công thức:
=EOMONTH(date,1)
Giải pháp thay thế thông minh:
Nếu bạn là người thích xây dựng các công thức thông minh để phân loại, bạn cũng có thể sử dụng hàm DATE trong Excel để lấy ngày cuối cùng của tháng:
=DATE(YEAR(date),MONTH(date)+1,0)
Thủ thuật với công thức này là cung cấp số 0 làm đối số ngày. Khi bạn cung cấp số 0 làm đối số ngày trong hàm DATE, hàm date sẽ quay trở lại ngày đầu tiên của tháng trước cho đến ngày cuối cùng của tháng đó. Vì vậy, bằng cách cộng thêm 1 vào đối số tháng, và sử dụng 0 cho đối số ngày, hàm DATE cuộn lại cho ngày cuối cùng của tháng “ban đầu”.
– Ví dụ 3: Bôi đen các ngày có cùng tháng và năm Công thức chung:
=TEXT(A1,”myyyy”)=TEXT(date,”myyyy”)
Giải thích:
Nếu muốn sử dụng định dạng có điều kiện để đánh dấu các ngày cùng tháng cùng năm với nhau, bạn có thể sử dụng công thức đơn giản dựa trên hàm TEXT.
=TEXT(B4,”myyyy”)=TEXT(DATE(2015,6,1),”myyyy”)
Sau khi lưu rule, bạn sẽ nhìn thấy tất cả các ngày cùng tháng 6 năm 2015 được đánh dấu.
Ý nghĩa công thức:
Công thức này sử dụng hàm TEXT để ghép tháng và năm của mỗi ngày. Sau đó, 2 ngày được kiểm tra sự bình đẳng. TEXT là một hàm hữu ích cho phép bạn chuyển đổi một số thành văn bản ở định dạng văn bản mà bạn chọn. Trong trường hợp này định dạng là định dạng ngày tùy chỉnh “myyyy”, có nghĩa là đối số tháng không có số 0 ở đầu và đối số năm có 4 chữ số. Ví dụ, nếu A1 chứa ngày 9/6/2015, TEXT (A1, “myyyy”) sẽ tạo chuỗi văn bản “62016”.
Sử dụng các ô khác cho đầu vào:
Bạn không cần mã hoá một ngày vào rule. Để tạo rule linh hoạt hơn, bạn có thể sử dụng các ô khác làm biến. Ví dụ, nếu bạn đặt tên cho ô E2 là “date”, bạn có thể viết lại công thức như sau:
=TEXT(B4,”myyyy”)=TEXT(date,”myyyy”)
Và bất cứ khi nào bạn thay đổi ngày trong E2, rule định dạng có điều kiện sẽ cập nhật ngay lập tức. Điều này đơn giản hóa công thức và giúp bạn đọc dễ hơn.
Giải thích:
Để tính tổng các giá trị giữa 2 ngày, bạn có thể sử dụng hàm SUMIFS. Trong ví dụ trên, ô H5 sử dụng công thức này:
Công thức này tính tổng số tiền trong cột D khi một ngày trong cột C nằm giữa một ngày trong cột H5 và một ngày trong cột H6. Trong ví dụ, H5 chứa ngày 15/9/2015 và H6 chứa ngày 15/10 /2015.
Để ghép khớp ngày giữa hai giá trị, chúng ta phải sử dụng hai tiêu chí. SUMIF yêu cầu mỗi tiêu chí phải được nhập dưới dạng một vùng tiêu chuẩn:
“
Lưu ý rằng phải bao gồm các toán tử logic trong ngoặc kép (“”) sau đó nối ghép các tham chiếu ô sử dụng dấu và (&).
– Ví dụ 5: Tính số ngày trong năm Công thức chung:
=date-DATE(YEAR(date),1,0)
Giải thích:
Nếu muốn tính số ngày từ một định dạng ngày cụ thể (tức là ngày thứ bao nhiêu trong năm), bạn có thể sử dụng công thức sử dụng kết hợp các hàm DATE và YEAR.
Ví dụ: với ngày 1/6/2016 trong ô B4, sử dụng công thức sau sẽ trả lại kết quả 153:
=B4-DATE(YEAR(B4),1,0)
Và ngày 1/6/2016 là ngày thứ 153 trong năm 2016.
Ý nghĩa của công thức:
Công thức này tận dụng lợi thế đó là thực tế ngày tháng chỉ là các số liên tiếp trong Excel. Nó xác định ngày cuối cùng của năm trước và trừ đi kết quả ngày ban đầu trong ô B4. Kết quả trả lại là ngày thứ bao nhiêu trong năm.
Lưu ý đối số ngày trong hàm DATE được cung cấp bằng 0. Hàm DATE còn có chức năng là có thể xử lý các giá trị DAY nằm ngoài phạm vi và điều chỉnh kết quả phù hợp.
Tính số ngày đến ngày hiện tại trong năm
Để điều chỉnh công thức trả lại kết quả tính số ngày kể từ ngày thứ n trong năm cho ngày hiện tại, chỉ cần sử dụng hàm TODAY:
=TODAY()-DATE(YEAR(TODAY()),1,0)
– Ví dụ 6: Chuyển đổi định dạng dd/mm/yy (ngày/tháng/năm) thành mm/dd/yy (tháng/ngày/năm) Công thức chung:
=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))
Giải thích:
Để chuyển đổi định dạng ngày tháng từ định dạng dd/mm/yy (ngày/tháng/năm) sang mm/dd/yy (tháng/ngày/năm), bạn có thể sử dụng công thức dựa trên hàm DATE. Trong ví dụ trên, công thức trong ô C5 là:
=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2))
Chuyển đổi các giá trị văn bản trong B5 29/02/16 thành một định dạng ngày thích hợp trên Excel.
Ý nghĩa của công thức:
Giá trị cốt lõi của công thức này là hàm DATE, được sử dụng để ghép một giá trị ngày thích hợp trên Excel. Hàm DATE yêu cầu giá trị năm, tháng và ngày hợp lệ, vì vậy chúng được phân tích cú pháp từ chuỗi văn bản ban đầu như sau:
Giá trị năm được trích bằng hàm RIGHT:
RIGHT(B5,2)+2000
Hàm RIGHT nhận được nhiều nhất 2 ký tự từ giá trị gốc. Số 2000 được thêm vào kết quả để tạo ra năm hợp lệ. Số này làm đối số năm trên hàm DATE.
Giá trị tháng được trích bằng:
MID (B5,4,2)
MID lấy 4-5 ký tự. Kết quả được đưa vào hàm DATE làm đối số tháng.
Giá trị ngày được trích bằng:
LEFT (B5,2)
LEFT lấy 2 ký tự cuối cùng của giá trị văn bản gốc, đưa vào hàm DATE làm đối số ngày.
Ba giá trị được trích ở trên được đưa vào hàm DATE như sau:
= DATE (2016, “02”, “29”)
Mặc dù tháng và ngày được cung cấp dưới dạng văn bản, hàm DATE sẽ tự động chuyển đổi sang số và trả về kết quả ngày hợp lệ.
Lưu ý: giá trị năm 2016 đã được tự động chuyển đổi thành số khi thêm 2000 vào.
Thêm vấn đề về dấu cách:
Nếu giá trị văn bản gốc ban đầu chứa thêm dấu cách ở đầu hoặc cuối, bạn có thể sử dụng thêm hàm TRIM để xóa:
=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))
– Ví dụ 7: Tạo hàng loạt ngày giống nhau, chỉ khác năm Công thức chung:
=DATE(YEAR(date)+1,MONTH(date),DAY(date))
Giải thích:
Nếu cần tạo một loạt ngày tháng giống nhau, có số năm tăng dần, bạn có thể sử dụng công thức có các hàm DAY, MONTH, YEAR, và DATE.
Ý nghĩa công thức:
Trong ví dụ trên, ngày trong ô B6 là ngày bắt đầu và công thức trong ô B7 là:
= DATE (YEAR (B6) + 1, MONTH (B6), DAY (B6))
Để giải quyết công thức này, Excel sẽ trích giá trị năm, tháng và ngày từ ngày bắt đầu trong ô B6, sau đó cộng thêm 1 vào giá trị năm. Tiếp theo, ngày kế tiếp được lặp lại bằng cách sử dụng DATE, sử dụng cùng một ngày và tháng, và và công thêm 1 vào năm.
= DATE (YEAR (B6) + 1, MONTH (B6), DAY (B6))
= DATE (2010 + 1,1,15)
= DATE (2011,1,15)
= 1/15/2011
Công thức đầu tiên sẽ trả về kết quả một định dạng ngày mới 1/15/2011, số năm nhiều hơn 1 năm từ định dạng ban đầu .
Sau khi nhập công thức đầu tiên, bạn có thể sao chép công thức xuống các ô tiếp theo. Mỗi công thức tiếp theo tạo ra một định dạng ngày mới có số năm tăng dần.
=DATE(YEAR(date)+1,1,1)
– Ví dụ 8: Tạo hàng loạt ngày và năm giống nhau, chỉ khác tháng Công thức chung:
=DATE(YEAR(date),MONTH(date)+1,DAY(date))
Giải thích:
Nếu muốn tạo một loạt ngày có công thức tăng dần các tháng, nhưng ngày và năm giống nhau bạn có thể sử dụng các hàm DAY, MONTH, YEAR và DATE.
Ý nghĩa công thức:
Trong ví dụ trên, định dạng ngày trong ô B6 là ngày bắt đầu và công thức sử dụng trong ô B7 là:
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
Để giải quyết công thức này, Excel sẽ trích giá trị năm, tháng và ngày từ ngày bắt đầu trong ô B6, sau đó cộng thêm 1 vào giá trị tháng. Tiếp theo, ngày kế tiếp được lặp lại bằng cách sử dụng DATE, sử dụng cùng một ngày và năm, và và cộng thêm 1 vào giá trị tháng.
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
=DATE(2010,1+1,15)
=DATE(2010,2,15)
=2/15/2010
Công thức đầu tiên sẽ trả về kết quả một định dạng ngày mới 2/15/2010, số tháng nhiều hơn 1 lần số tháng từ định dạng ban đầu.
Sau khi nhập công thức đầu tiên, bạn có thể sao chép công thức ở các ô tiếp theo. Mỗi công thức tiếp theo tạo ra một định dạng ngày mới có giá trị tháng tăng dần.
Lưu ý: nếu định dạng ngày bắt đầu từ 31/1, công thức trên sẽ bỏ qua tháng 2 và chuyển tiếp sang tháng 3. Lý do là vì không có ngày 2/31/2010, do đó Excel sử dụng giá trị ngày để chuyển sang ngày 3/3/2010.
= EOMONTH (B6,1)
Giải thích:
Nếu muốn tính tổng theo năm, bạn có thể sử dụng hàm SUMIFS với 2 tiêu chí.
Trong ví dụ trên, công thức trong ô C6 là:
Kết quả là tổng số tiền trong năm 2011. Bạn có thể sao chép công thức để tính tổng số tiền cho năm 2012 và 2013.
Ý nghĩa công thức:
Đối số đầu tiên của SUMIFs là phạm vi tổng (“sum_range”), và các tiêu chí được cung cấp dưới dạng một hoặc nhiều cặp vùng tiêu chuẩn.
Trong ví dụ này, phạm vi tổng là một vùng có tên gọi “amount” (E3:E2931), và các tiêu chí được cung cấp theo hai cặp, cả hai đều sử dụng một vùng có tên gọi là “date” (B3: B2931).
Trong mỗi trường hợp, hàm DATE được sử dụng trong các tiêu chí để xây dựng 2 định dạng ngày hợp lệ, cả hai đều sử dụng cùng một năm:
1. Ngày đầu tiên của năm 2011
2. Ngày cuối cùng của năm 2011
Kết quả công thức trả về là tổng số tiền tất cả các khoản chỉ trong năm 2011.
Vì đang sử dụng một tham chiếu ô để cung cấp kết quả cho một năm, bạn cùng có thể sao chép công thức để tính tổng giá trị cho các năm 2012 và 2013.
Giải thích:
Để tính tổng giá trị các ngày khác nhau, bạn có thể sử dụng hàm SUMIF.
Trong ví dị trên, công thức trong ô H5 là:
Công thức này tính tổng số tiền trong cột D khi ngày trong cột C lớn hơn ngày 1/10/2015.
Lưu ý rằng phải bao gồm các toán tử logic trong ngoặc kép (“”) sau đó nối ghép các tham chiếu ô sử dụng dấu và (&).
Ngày là tham chiếu ô
Nếu bạn muốn hiển thị ngày trên bảng tính để có thể dễ dàng thay đổi, bạn sử dụng công thức này:
Trong đó A1 là tham chiếu đến ô có chứa ngày hợp lệ.
Hàm SUMIFS thay thế:
Ngoài ra bạn cũng có thể sử dụng hàm SUMIFS. Hàm SUMIFS có thể xử lý nhiều tiêu chí, và thứ tự của các đối số khác với SUMIF. Công thức hàm SUMIFS tương đương là:
Lưu ý rằng phạm vi tổng hợp luôn đứng đầu tiên trong hàm SUMIFS.
– Ví dụ 11: Chuyển đổi văn bản thành ngày Công thức chung:
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Giải thích:
Để chuyển đổi văn bản ở định dạng ngày không phù hợp thành định dạng ngày phù hợp trên Excel, bạn có thể phân tích cú pháp văn bản và ghép thành một ngày phù hợp với công thức dựa trên một số hàm như: DATE, LEFT, MID và hàm RIGHT.
Lưu ý: Trước khi bạn sử dụng công thức, tham khảo một số cách khác bên dưới để kiểm tra Excel phát hiện ra các văn bản và ngày không có công thức.
Trong ví dụ được trên, công thức trong ô C6 là:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Công thức này trích các giá trị năm, tháng và ngày tách biệt, và sử dụng hàm DATE để tập hợp cvào ngày 24 /10/2000.
Excel sẽ không nhận ra các giá trị văn bản này là định đạng ngày, do đó, để tạo một ngày hợp lệ, bạn cần phải phân tích cú pháp văn bản thành các thành phần (năm, tháng, ngày) và sử dụng các thành phần này để tạo một ngày bằng hàm DATE.
Ý nghĩa công thức:
Hàm DATE lấy 3 đối số: năm, tháng và ngày. Hàm LEFT trích 4 ký tự còn lại và cung cấp làm đối số năm cho hàm DATE. Hàm MID trích 5-6 ký tự và lấy các ký tự này là đối số tháng, ngày cho hàm DATE, và hàm RIGHT trích 2 ký tự bên phải và cung cấp đối số ngày cho hàm DATE. Kết quả cuối cùng là một định dạng ngày hợp lệ trên Excel, có thể được định dạng bằng bất kỳ định dạng nào mà bạn muốn.
Ví dụ khác:
Trong hàng số 8, định dạng ngày là chúng tôi (ngày. tháng. năm) không được Excel công nhận, và công thức trong C8 là:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Thêm số 0 để chuyển đổi định dạng văn bản thành định dạng ngày:
Trong một số trường hợp bạn sẽ nhận thấy rằng có định dạng văn bản mà Excel sẽ nhận ra. Trong trường hợp nàu bạn có thể buộc Excel chuyển đổi các giá trị văn bản thành định dạng ngày tháng bằng cách thêm số 0 vào giá trị. Khi bạn thêm số 0, Excel sẽ cố gắng buộc các giá trị văn bản thành các con số.
Để chuyển đổi định dạng văn bản thành định dạng ngày bằng cách thêm số 0, bạn sử dụng Paste Special:
Bước 1: Thêm số 0 vào ô không sử dụng và sao chép vào clipboard.
Bước 2: Chọn ngày bị lỗi.
Để chuyển đổi các ngày bằng cách thêm số 0 bằng cách sử dụng công thức:
=A1+0
Trong đó A1 có chứa định dạng ngày không hợp lệ.
Chuyển đổi Text thành Column:
Một cách khác để Excel nhận biết định dạng ngày tháng là sử dụng text thành các tính năng column:
Đôi khi bạn có thể sử dụng giải pháp này để sửa mọi thứ cùng một lúc.
Giải thích:
Lưu ý: Excel được tích hợp một số quy tắc xác nhận dữ liệu hợp lệ cho định dạng ngày tháng.
Để cho phép người dùng chỉ nhập đối số ngày giữa hai ngày, bạn có thể xác nhận dữ liệu bằng một công thức tùy chỉnh dựa trên hàm AND.
Trong ví dụ trên, việc xác nhận dữ liệu được áp dụng cho C5: C9 là:
Ý nghĩa công thức:
Các quy tắc xác nhận dữ liệu được kích hoạt khi người dùng thêm hoặc thay đổi giá trị trong ô.
Hàm AND lấy nhiều đối số (logic) và trả về kết quả TRUE chỉ khi tất cả các đối số trở về TRUE. Hàm DATE tạo ra một ngày hợp lệ trên Excel với các giá trị năm, tháng, và năm.
Nếu chỉ muốn cho phép định dạng ngày tháng trong tháng 6 năm 2016, bạn sử dụng hàm AND với 2 đối số.
Đối số đầu tiên kiểm tra đầu vào C5 lớn hơn hoặc bằng ngày 1/6/2016:
Đối số thứ 2 kiểm tra tính hợp lý đầu vào C5 nhỏ hơn hoặc bằng ngày 30/6/2016:
Nếu cả hai điều kiện là TRUE, hàm AND trả về kết quả TRUE và đầu vào xác nhận hợp lệ. Nếu một trong hai điều kiện là FALSE, và kết quả trả về FALSE và đầu vào dữ liệu không thành công.
– Ví dụ 13: Thêm năm vào ngày Công thức chung:
=DATE(YEAR(date)+years,MONTH(date),DAY(date))
Giải thích:
Để thêm năm cho một ngày cụ thể, bạn có thể sử dụng công thức dựa trên hàm DATE kết hợp với các hàm YEAR, MONTH, DAY.
Trong ví dụ trên, công thức trong ô D5 là:
=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))
Ý nghĩa công thức:
Làm việc từ trong ra ngoài, các hàm YEAR, MONTH, DAY trích các thành phần ngày tương ứng:
=YEAR(B5)
=MONTH(B5)
=DAY(B5)
Xét về mặt bề ngoài hàm DATE chỉ đơn giản gộp các giá trị của thành phần thành một định dạng ngày Excel hợp lệ. Để thêm năm cho một ngày cụ thể, bạn chỉ cần thêm giá trị trong C5 vào thành phần năm trước khi gộp:
=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))
Công thức có dạng như sau:
=DATE(1960+10,3,8)
=DATE(1970,3,8)
=8-Mar-1970
https://thuthuat.taimienphi.vn/ham-date-trong-excel-cach-su-dung-cau-truc-ham-23781n.aspx
Bạn đang đọc nội dung bài viết Create A Date Sequence In Excel And Auto Fill Date Series trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!