Data Validation the Date of Current year

This post will guide you how to allow a user to enter only dates of current year in excel. How do I set the limitation to allow a user to enter the date of current year in a cell in excel. And this post will talk that how to use data validation with a custom formula based on the YEAR function to allow only to enter the date of the current year.

Data Validation the Data of Current year


If you want to allow only the date of the current year in a certain cells in your worksheet, just do the following steps:

#1 select the range of cells in a column that you want to set data validation.

data validation current year1

#2 go to DATA tab, click Data Validation command under Data Tools group. The Data Validation dialog box will appear.

#3 select Custom option from the Allow drop-down list.

data validation current year2

#4 type the following formula into the Formula: text box. Then click OK button.

=YEAR(B1)=YEAR(TODAY())

data validation current year3

#5 you can try to enter one previous year or future year, you will get a warning message box. data validation current year4

 

Related Posts
How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

How to Count Dates of Given Year in Excel
count dates of given year7

This post will guide you how to count Dates of a certain year in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by a given year in Excel. And ...

How to Sum Values Based on Month and Year in Excel
Sum Values Based on Month 6

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product ...

How to Convert Date & Time Format to Date in Excel
Convert Date & Time Format to Date 7

Sometimes we want to convert date and time format to date only format in excel for example convert 01/29/2019 06:51:03 to 01/29/2019, we can convert format by Formula or Format Settings. The two ways are easy to learn, so you ...

How to Extract Year from Date & Time Format in Excel
Extract Year from Date & Time Format 6

Sometimes we want to get Year information from date and time format to show the Year only in excel. For example convert 01/29/2019 06:51:03 to 2019, we can get Year information by Formula or Format Settings. The two ways are ...

How to Calculate Remaining Days in a Month or Year in Excel
calculate remaining days5

This post will guide you how to calculate remaining days in a given month or year in Excel. How do I calculate the number of days left in a month or year using a formula in Excel 2013/2016. Calculate Remaining ...

How to Split Date into Day, Month and Year in Excel
split date into day month year10

This post will guide you how to split date into separate day, month and year in excel. How do I quickly split date as Day, Month and Year using Formulas or Text to Columns feature in Excel. Split Date into ...

Sidebar