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.
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:
Next, enter into cell D2 the following formula that builds on the helper columns:
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.
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:
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:
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.
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.