Bạn đang xem bài viết Create A Date Sequence In Excel And Auto Fill Date Series được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.
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
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ế.
Hàm Date Trong Excel, Cách 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.
Một số ví dụ về hàm Date – Ví dụ 1: Đánh dấu ngày lớn hơn Công thức chung:
=A1gt;DATE(year,month,day)
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:
=B4gt;DATE(2015,8,1)
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:
=B4gt;=DATE(2015,8,1)
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:
=B4gt;input
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.
– Ví dụ 4: Tính tổng giá trị theo ngày Công thức chung:
=SUMIFS(amount,start_date,”gt;”amp;A1,end_date,”lt;gt;
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:
=SUMIFS(amount,date,”gt;”amp;H5,date,”lt;gt;
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.
Ý nghĩa của công thức
Hàm SUMIFS hỗ trợ toán tử logic ( logical operator) của Excel (tức là “=”, “gt;”, “gt; =”,…) và nhiều tiêu chí khác.
Để 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:
“gt;”amp;H5,date
“lt;”amp;h6,date criteria=””gt;
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à (amp;).
Cần chú trọng khi sử dụng dấu lớn hơn hoặc bằng (“gt; =”) và ít hơn hoặc bằng (“lt;=”) nếu=”” muốn=”” bao=”” gồm=”” (ghép)=”” ngày=”” bắt=”” đầu=”” hoặc=”” ngày=”” kết=”” thúc=”” cũng=”” như=”” ngày=”” giữa=””gt;
– 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)
– Ví dụ 9: Tính tổng theo năm Công thức chung:
=SUMIFS(sum_range,date,”gt;=”amp;DATE(year),date,”lt;gt;
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à:
=SUMIFS(amount,date,”gt;=”amp;DATE(G6,1,1),date,”lt;gt;
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
date, “gt;=”amp;DATE(G6,1,1)
date, “lt;=”amp;date(g6,12,31) date=”” is=””gt;lt;=gt;
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.
– Ví dụ 10: Tính tổng các ngày khác nhau Công thức chung:
=SUMIF(range,”gt;”amp;DATE(year,month,day),sum_range)
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à:
=SUMIF(date,”gt;”amp;DATE(2015,10,1),amount)
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.
Ý nghĩa công thức:
Hàm SUMIF hỗ trợ toán tử logic của Excel (tức là “=”, “gt;”, “gt; =”, …), do đó bạn có thể sử dụng hàm theo ý muốn của mình.
Trong ví dụ này để khớp các ngày lớn hơn ngày 1/10/2015, bạn sử dụng toán tử lớn hơn (gt;) với hàm DATE để tạo ngày:
“gt;”amp;DATE(2015,10,1)
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à (amp;).
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:
=SUMIF(date,”gt;”amp;A1,amount)
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à:
=SUMIFS(amount, date,”gt;”amp;DATE(2015,10,1))
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.
Bước 3: Chọn Paste Special =gt; Values =gt; Add .
Để 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:
Chọn cột ngày tháng, sau đó thử Data =gt; Text to columns =gt; Fixed =gt; Finish.
Đô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.
– Ví dụ 12: Xác nhận giá trị hợp lệ giữa các ngày Công thức chung:
=AND(A1gt;=date1),A1lt;gt;
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à:
=AND(C5gt;=DATE(2016,6,1),C5lt;gt;
Ý 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:
C5gt;=DATE(2016,6,1)
Đố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:
Hướng Dẫn Cách Sử Dụng Hàm Date Trong Excel Thông Qua Ví Dụ
Hàm DATE là hàm Excel về ngày tháng năm. Hàm DATE sẽ trả về kết quả là ngày tháng năm theo định dạng tương ứng với ngày tháng năm được nhập vào dựa trên lịch ngày tháng năm tiêu chuẩn. Bài viết này Nguyễn Hùng sẽ hướng dẫn cách dùng hàm DATE trong Excel dựa trên các ví dụ cụ thể.
Cú pháp: =DATE(year, month, day) Giải thích cú pháp:
year: Giá trị của đối số năm gồm từ 1 đến 4 chữ số (0~9999). Tùy thuộc vào ngày tháng năm của hệ thống máy tính bạn đang dùng. Theo mặc định trên Windows thì Microsoft Excel sử dụng hệ thống từ năm 1900. Nghĩa là ngày tháng năm đầu tiền là ngày 1 tháng 1 năm 1900. Đây là giá trị bắt buộc.
Nếu giá trị year từ 0 đến 1899 (gồm cả 0 và 1899) thì Excel sẽ cộng thêm 1900 vào giá trị đó để tính năm. VD: =DATE(185,5,18) giá trị trả về là ngày 18 tháng 5 năm 2085 (1900+185).
Nếu giá trị year từ 1900 đến 9999 (gồm cả 1900 và 9999) thì Excel sẽ lấy giá trị đó làm giá trị năm. VD: =DATE(2016,8,22) giá trị trả về là ngày 22 tháng 8 năm 2016.
Nếu year nhỏ hơn 0 và lớn hơn 10000 thì Excel sẽ trả về giá trị lỗi #NUM!
month: Giá trị của đối số tháng. Là số nguyên dương hoặc âm đại diện cho tháng trong năm từ tháng 1 đến tháng 12. Đây là giá trị bắt buộc.
Nếu giá trị month lớn hơn 12 thì Excel sẽ quy đổi 12 tháng là 1 năm và tăng số năm lên. VD: =DATE(2015,18,20) giá trị trả về sẽ là ngày 20 tháng 6 năm 2016.
Nếu giá trị month nhỏ hơn 1 thì Excel sẽ quy đổi 12 tháng là 1 năm và giảm số năm đi. VD: =DATE( 2017,-4,10) giá trị trả về sẽ là ngày 10 tháng 8 năm 2016.
day: Giá trị của đối số ngày. Là số nguyên dương hoặc âm đại diện cho ngày từ 1 đến 31. Đây là giá trị bắt buộc.
Nếu giá trị day lớn hơn số ngày trong tháng quy định thì sẽ được đẩy sang tháng tiếp và số tháng sẽ được tăng lên theo quy luật của giá trị month, nếu tháng tăng lên quá so với quy định thì sẽ đẩy năm lên theo quy luật của giá trị year. VD: =DATE(2015,12,35) giá trị trả về sẽ là ngày 4 (35-31) tháng 1 năm 2016
Nếu giá trị day nhỏ hơn 1 thì giá trị của day là ngày của tháng đó trừ đi độ lớn giá trị nhập và lùi tháng so với quy định của month và năm cũng sẽ lùi theo cho phù hợp với quy luật của year (nếu có). VD: =DATE(2016,1,-12) giá trị trả về sẽ là ngày 19 tháng 12 năm 2015.
Cài đặt định dạng ngày tháng năm hiển thị kết quả trong Excel
B2: Chọn tab Date và chọn định dạng cho phù hợp như sau:
Tính toán ngày tháng năm dựa trên ô ngày tháng năm khác
Trong Excel ta có thể dùng hàm DATE để tính toán ngày tháng năm trong 1 ô dựa trên ngày tháng năm ở 1 ô khác.
Ví dụ sau ta sẽ tính ngày kỷ niệm 5 năm thành lập như sau:
Cú pháp hàm DATE ô F4 như sau: =DATE(YEAR(D4)+5,MONTH(D4),DAY(D4))
Trong đó:
Cú pháp hàm DATE: =DATE(year,month,day)
Giá trị year sẽ là YEAR(D4)+5. Ở đây sẽ là giá trị năm ở ô D4 là 2015+5=2020
Giá trị month là MONTH(D4). Kết quả là tháng ở ô D4 là tháng 4.
Giá trị day là DAY(D4). Kết quả trả về là ngày ở ô D4 là ngày 2.
Cập nhật thông tin chi tiết về Create A Date Sequence In Excel And Auto Fill Date Series trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!