Microsoft

Determine the remaining number of days in the same year

Susan Harkins explains two ways to calculate in Excel the remaining number of days from a specific date to the end of the same year.

Date arithmetic is easy, once you understand the underlying concept. You anchor a spot on a linear timeline. (I added linear for all you time continuum geeks.) For instance, today can be an anchor between yesterday and today, the first day of the year and the last day of the year, and so on. You just have to anchor the right spot on that timeline.

Today, I'm going to show you two ways to determine the remaining number of days in the same year, given a specific date (the anchor). There's an easy way that uses helper columns and a more complex formula that does everything on its own. Both work fine but the helper column, within the context of this discussion is a visual way to help you breakdown the complex formula.

Helper formula

First, let's look at the helper column formula. Column A in the sheet below displays a series of dates, in no particular order. Notice that they're not all from 2013 either. Columns B and C use the YEAR() and DATE() functions to return the date's year and the last day of the same year. Specifically, enter the following formulas in cells B2 and C2, respectively, and then copy those formulas to the remaining cells in each column:

=YEAR(A2)
=DATE(B2,12,31)

Next, enter into cell D2 the following formula that builds on the helper columns:

=C2-A2

The function in column B determines the date's year. The function in column C calculates a date - the last day of the year - using the year value in column B. Finally, the simple expression in column D subtracts the original date in column A from the newly generated last day of the year date in column C. The result is the number of days remaining in the same year.

More complex

You can do all of that with one formula - and while I called it complex earlier, it really isn't all that complicated. In cell E2, enter the following formula:

=DATE(YEAR(A2),12,31)-A2

And copy it to the remaining cells in the column. As you can see, one formula can easily handle this calculation. All I've done is combined the tasks from columns B, C, and D. The DATE() function generates the last-day-of-the-year date by  using the YEAR() function as its first argument. The final component, -A2 subtracts the original date from the last-day-of-the-year date, the same way the helper functions did.

It's worth mentioning that in both cases, the final result reflects the number of remaining days in the same year as the original date. It isn't the remaining number of days until the end of the current year. If you need a value based on the current year, you'd use the formulas:

=YEAR(TODAY())
=DATE(YEAR(TODAY()),12,31)-A2

These will return negative values for dates in future years.

When faced with a difficult date (or any) calculation, breaking things down into helper columns can help. You can keep the helper columns or combine them once you've worked out the calculations. There's no right or wrong - it's all about getting the correct information as efficiently as possible.

Sample Excel worksheets are provided for your convenience.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
lee_dimambro
lee_dimambro

The Weekday function will return a number. To get the full spelling of the day custom format the cell using DDDD and the name of the day will be spelled out. For example the cell value of 26/03/2013 custom formatted as DDDD the day will display as Tuesday. To get the short form of the day custom format the cell as DDD. With the above date the cell will display Tue

lee_dimambro
lee_dimambro

The following formula will return "Today is Tuesday, March 26, 2013" ="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy") The following formula will return "Tuesday" =TEXT(TODAY()," dddd")

Scalloway1
Scalloway1

I would do it as a VLookup. Create a short table on a separate worksheet thus: 1 Sunday 2 Monday etc. Then in the column you want the day of the week put this formula =VLOOKUP(address of cell to be looked up, address of 1st cell of lookup list:address of Final cell of lookup list,2)

rowlynn
rowlynn

This is very timely. I am wondering now, how do I get the day of the week assigned to the date. If I use the "weekday" function to identify the day it comes up in numeric form. Is there a way to convert this, or set the value of the column after the date, to Sunday, Monday, etc. without another function as an if/then statement, and without re-listing the date?

lee_dimambro
lee_dimambro

The following formula will return the number of days remaing from today to the end of the year. For example if today's date is Mar 25, 2013 the formula will returen 281 indicating the number of days remining in 2013. Note that the cell format must be a number format =DATE(YEAR(TODAY()),12,31)-TODAY() To get the number of days that have elapsed year to date use the following formula. =TODAY()-DATE(YEAR(TODAY()),1,0) For example if today's date is Mar 25, 2013 the formula will returen 84 indicating the number of days that have elapsed in 2013. By subtracting 84 from 365 gives the number of days remaining in 2013 365 - 84 = 281

Scalloway1
Scalloway1

Put in the first date and use Autofill to to drag down to the cells below. Excel should automatically increment the date, changing to 1 March when you go by the last day in February.

myMary
myMary

Thanks, Susan, this is great info! Now, how about if I want to enter a starting date in $E$3 on an Excel expense report (like 2/1/2013). And down a row in Col A, I want to fill in the dates for that month($E$3). Of course, if it's a Leap Year, I want to end at 2/29, otherwise I want to end with 2/28 for February. Similarly, if it is any other month in the year, I want to end either on the 30th or 31st, as appropriate for each month. Any hints?