To calculate the no. of day in a year from a particular date, we will use the DATE Function and YEAR Function.
Here’s an example, to elaborate the steps that needs to be done:
As you can see in the image above, we have taken a bunch of dates, and we are going to calculate the Nth (number) day of the year.
Step 1: Input the formula =B3-DATE(
The first thing we will do is input the equal to sign as that is how a formula begins. Then after the same, we will select the date. In the above example, cell B3 is the date from which we want to find out the Nth day of the year.
Then we will input a minus sign and then we will input the DATE Function.
=DATE(
Step 2: Input the required values in the formula
The following formula requires year, month and day.
We have used a minus sign as we are going to calculate the Nth day of the year, so we will be subtracting a few things from the actual date to bring out the result.
Step 3: Input the formula =YEAR(
After inputting the DATE Function, we will input the YEAR Function i.e. as follows:
=YEAR(
Step 4: Input the required values in the formula
The following formula requires serial number.
The serial number will be the date we selected before i.e. cell B3.
Then we will close the YEAR Function and move forward with the DATE Function. In the DATE Function, we need to input a value in the month, so we will be inputting 1 in the same. This is being done because we only need the Nth day of the year.
And when it comes to the aspect of day, we will jot down 0 in the same.
Step 5: Cross- check the formula and proceed
So, the overall formula becomes as follows:
=B3-DATE(YEAR(B3),1,0)
Now press enter and BAAAMMM!!! The answer is on the screen i.e. 295.
The date which is 22 October 2019, if converted, is basically the 295th day of the year.
Now, all you have to do is drag down the function, so that you can convert the other dates into the Nth day of Year as well.
It’s easy, isn’t it?
