In many industries, such as finance or accounting, being able to work with dates is an important skill. In particular, being able to find the first day of the year is a common task, especially in accounting. In this tutorial, you’ll learn how to get the first day of the year in Excel.
There are a number of easy ways in which you can get the first day of the month. By the end of this tutorial, you’ll have learned how to do the following:
- Use the
DAY()function to get the first day of the year - Use the
TODAY()function to get the first day of the current year
How to Get First Day of the Year in Excel Using DAY()
How can you get the first day of the year in Excel?
To get the first day of the year in Excel of a given date, you can pass the date into the following function: DATE(YEAR(A2), 1, 1). This extracts the year from the date and sets the month and day to January 1.
Excel makes this operation incredibly simple. Let’s take a look at an example of how we can get the first day of the year for an entire column of data in Excel.
How to Get First Day of the Year in Excel
Load your dataset with the dates in a column
In the example dataset, we have have a dataset with two columns: the first contains the date we want to use and the second is a placeholder to store the first day of the year for the given date. The dates range from 2014 through 2030.
Use the DATE() and YEAR() functions to extract the year
Using the function =DATE(YEAR(A2), 1, 1) we extract only the year from the provided date and reset the month and date to January 1. This gets the first date of the year.
Drag the fill handle down to get the first day of the year for all cells
Click the Fill Handle (the crosshairs) and drag it down to the end of the values. When you let go of the mouse, the formulas are copied down, providing you with the first day of the year for each of the given dates.
In the following section, you’ll learn how to get the first day of the current year.
How to Get the First Day of the Current Year in Excel
Excel makes it easy to get the first day of the current year by combining the method you learned above with the TODAY() function. The TODAY() function is used to return the current date. Because of this, we can simply pass the function into our earlier method. Let’s take a look at what this formula looks like:
=DATE(YEAR(TODAY()), 1, 1)Let’s take a look an example of what this looks like in Excel:
Step 1: Enter the Formula Into a Cell
Enter the formula we explored earlier into a cell. Since we’re using the TODAY() function, it doesn’t matter which cell you use.
Step 2: Hit the Return Key to Get the First Day of the Current Year
When you hit the Return key, the function evaluates and returns the first day of the current year.
Conclusion
In this tutorial, you learned how to use Excel to get the first day of the year for a given date. Being able to work with dates is an essential skill when working in Excel. Being able to convert a date into the first day of the year opens up many different possibilities! In this tutorial, you first learned how to convert a column of dates into their first-day-of-the-year counterparts. In order to this, you used the DATE() and YEAR() function. From there, you learned how to use the method to get the first day of the current year, by making use of the TODAY() function. This, for example, can be useful to figure out how many days have passed in a given year.
Additional Resources
To learn more about related topics, check out the tutorials below: