Dates are one of the common types of data stored in Excel worksheets. If you need to work with dates in some manner, you need to understand how to pull those dates apart in formulas. Here are ideas, tricks, and pointers on how you can better manipulate the dates you face working with.

Tips, Tricks, and Answers

The following articles are available for the 'Date Formulas' topic. Click the article''s title (shown in bold) to see the associated article.

   Adding Ordinal Notation to Dates
Want to add an ordinal suffix to a number, as in 2nd, 3rd, or 4th? Excel doesn't provide a way to do it automatically, but the ideas presented here can be helpful in devising a way to get the desired notation.

   Advancing Dates to a New Year
If you store dates in your worksheets, you may want to update those dates at the end of the year. This tip explains multiple ways that you can increment the year for those dates.

   Ages in Years and Months
Calculating an age is a common task when working with dates. If you want to figure out the number of years and months between two dates, you'll appreciate the discussion in this tip.

   Automatically Advancing by a Month
Excel allows you to perform quite a few operations using dates in your worksheet. Sometimes, however, the answer may not be immediately obvious. For instance, if you want to perform an operation that always advances the date to the beginning of the next month once half the month has passed.

   Backwards Date Parsing
Enter information into a worksheet, and you come to anticipate (and count on) how Excel will interpret that information and store it in the worksheet. If you suddenly find that Excel isn't parsing your date entries correctly, there are two possibilities you should check out right away.

   Beginning of a Future Week
Because Excel stores dates internally as serial numbers, it makes doing math with those dates rather easy. Even so, it can be confusing to figure out how to calculate a date in the future, particularly when you need to take the day of the week into consideration. This tip walks you through one such potentially confusing calculation.

   Calculating a Date Five Days before the First Business Day
Excel allows you to perform all sorts of calculations using dates. A good example of this is using a formula to figure out a date that is five days before the first business day of any given month. This tip shows how easy Excel makes such a calculation.

   Calculating a Group Retirement Date
Calculating a retirement date can be as simple as doing some date math to see when a person reaches a certain age. Calculating a date after the retirement age is reached is a bit more complex.

   Calculating a Sum for a Range of Dates
If you use Excel to track information based on dates, you may wonder how to get a sum for only certain dates that you tracked. Fortunately, Excel provides a couple of summing functions that can make quick work of summing values based on the criteria you specify.

   Calculating an Age On a Given Date
Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based on those dates. There are a few ways you can accomplish this in Excel, as described in this tip.

   Calculating Averages by Date
When you have a huge amount of daily data to analyze, you may want to calculate an average of values for any given date in the data. This may seem daunting but can be done relatively easily using the formulas described in this tip.

   Calculating Business Days
There are calendar days and then there are business days. Excel provides two functions (NETWORKDAYS and NETWORKDAYS.INTL) that are helpful to figure out how many business days there are between two dates.

   Calculating Dates for Thanksgiving
Ever wonder how to calculate the date for Thanksgiving in the United States? In this tip you discover not only that, but also how to figure it out for five consecutive years.

   Calculating Differences in Months using Non-Standard Date Values
Dates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, though, as demonstrated in this tip.

   Calculating Fractions of Years
When working with dates and the relationship between dates, Excel provides a variety of worksheet functions that may prove helpful. One such function is YEARFRAC, which allows you to calculate what fraction of a year is represented by the number of days between two dates.

   Calculating Future Workdays
Need to calculate the date that is a certain number of workdays in the future? You can do so using a couple of different worksheet functions, as described in this tip.

   Calculating Months for Billing Purposes
Different businesses have different ways to calculate elapsed time for billing purposes. Figuring out a formula that reflects your company's calculation method can be challenging.

   Calculating Months of Tenure
Need to know the number of months between two dates? It's easy to figure out if you use the DATEDIF function.

   Calculating the First Business Day of the Month
Want to know which day of the month is the first business day? The easiest way to determine the date is to use the WORKDAY function, as described in this tip.

   Calculating the First Tuesday
Do you need to figure out the date for the first Tuesday of any given month? Excel is incredibly flexible when it comes to formulas to return the desired date. This tip provides access to a plethora of such formulas.

   Calculating the Last Day in a Week Number
Given a particular week number for a year, you may want to figure out the date of the last day in that week. There is no intrinsic function to derive the desired date, but you can use one of the formulas presented in this tip.

   Calculating Week-Ending Dates
When working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several formulaic ways to accomplish this task, as described in this tip.

   Calculating Weekend Dates
Do you look forward to the weekend? Well, you can use Excel to let you know when the next weekend begins. Here's how you can create a formula that will tell you the next Saturday after a given date.

   Changing How Excel Determines which Year to Use
When you enter a date into a cell and you omit the year, Excel helpfully adds the current year to the date. If you want to modify how the year is added, you'll appreciate this tip.

   Converting an Unsupported Date Format
Excel makes it easy to import information created in other programs. Converting the imported data into something you can use can present a challenge. Here's how to make a change from an unsupported date format to one that Excel can work with.

   Converting Coded Dates into Real Dates
Sometimes the format in which you receive data is not the same format that would be optimal for Excel. For instance, you might receive some data that shows dates in a strange format, such as '04C18'. This tip explains how you can convert such strange formats into something you can use.

   Converting Dates to Text
Need to use a date as part of a larger string of text? Here are some handy ways to go about the process.

   Converting European Dates to US Dates
Those in Europe use a date format that is different than those in the US; this is not news. But what if you need to convert those European dates to US dates? There are several ways you can make the conversion, depending on your needs.

   Converting Mainframe Date Formats
Different industries and different computer systems specify dates in all sorts of strange ways. If you need to convert a date from an off-beat format into one that Excel can understand, you'll like the technique used in this tip.

   Converting Time Notation to Decimal Notation
Want to convert an elapsed time, such as 8:37, to a decimal time, such as 8.62? If you know how Excel stores times internally, coming up with the conversion formula is a snap.

   Converting UNIX Date/Time Stamps
If you import information generated on a UNIX system, you may need to figure out how to change the date/time stamps to something that Excel can recognize and work with. The conversion is easy, once you understand the way in which the date/time stamps are figured.

   Copying Dates a Year Into the Future
Need to copy a range of dates and update them to a different year? Here are a number of ways to accomplish this task with a minimum of fuss.

   Counting Dates in a Range
Excel makes working with a list of dates relatively easy. If you have a list of dates, you may need to know how many of those dates fall within a certain range. Here's how to get the count you need.

   Counting Month Ends
When working with dates in a worksheet, you may need to do some rather esoteric calculations on those dates. This tip looks at one such case: the need to calculate the number of month-ending dates between a beginning and an ending date.

   Date for Next Wednesday
When working with dates, it is often helpful to be able to calculate some date in the future based on a starting date. Figuring out future days of the week based on some starting date can be trickier still. Here's a wide range of formulas that can help you figure out the date of the next Wednesday, starting from any date.

   Days Left in the Year
Sometimes it is handy to know how many days are left in the current year. This tip provides a quick formula that indicates the number remaining.

   Deciphering a Coded Date
It is no secret that Excel allows you to work with dates in your worksheets. Getting your information into a format that is recognized by Excel as a date can be a challenge, at times. Here is an examination of how to make sense out of a coded date so it can be worked with in Excel.

   Determining Business Quarters from Dates
Many businesses organize information according to calendar quarters, especially when it comes to fiscal information. Given a particular date, you may wonder how you can determine the quarter of the year in which that date falls. There are a number of ways you can devise formulas for such an answer.

   Determining Contract Weeks
Everyone seems to determine the difference between dates differently. Nicole has a need to calculate contact weeks (the number of weeks between two dates) in a way specific to her company. This tip examines how this can be done.

   Determining If a Date and Time is within Working Hours
Excel is great at working with times and dates. Sometimes, though, it can be a bit tricky to figure out how to work with both times and dates at the same time. This tip examines one situation in which checking both must occur.

   Determining If a Date is between Other Dates
Need to figure out if one date is between two other dates? There are a wide variety of formulaic approaches you could use to determine the answer. Here are two short and elegant approaches.

   Determining If a Year is a Leap Year
Need to figure out if a given year is a leap year? It's not as easy as you think! This tip provides a plethora of ways that you can accurately make the determination.

   Determining Month Names for a Range of Dates
Given a starting date and an ending date, you may want to generate the names of all the months between those two dates. This tip examines several ways you can determine the information.

   Displaying a Number as Years and Months
How do you display a number of years, such as 3.67 years, as a number of years and months? It’s simple to do with a formula, as discussed in this tip.

   Elapsed Days as Years, Months and Days
Need to know how many days there are between two dates? It's easy to figure out—unless you need the figure in years, months, and days. Here's why.

   End-of-Month Calculations
Don't want to use the EOMONTH function to figure out the end of a given month? Here are some other ideas for discovering the date you need.

   Entering Dates without Separators
When doing data entry into a worksheet, you might want to enter dates without the need to type the separators that are normally part of those dates. Here's a discussion on how this can be done in Excel, along with the benefits and drawbacks of using such an approach.

   Finding the Dates for Minimums and Maximums
If you use Excel to maintain a collection of data, you may need to find information in one column based on information in an adjacent column. This tip shows a couple of approaches to getting the information you need.

   Finding the Previous Work Day
Simple math will tell you what the previous day is (just subtract 1 from today's date). What if you want to know the date of the previous workday, however? That can be almost as easy as simple math.

   Forcing Dates Forward
Want to push a date to some pre-defined day of the month? Here are some ways to force the issue.

   Generating a 4 On/4 Off Work Schedule
You can use Excel to work with times and dates. Sometimes, however, figuring out the best way to do that can be tricky. This tip examines one such situation when you need to create a 4 on/4 off schedule for a worker.

   Including Weeks in Elapsed Time
When showing how much time has elapsed between two dates, it is sometimes helpful to express the result in terms of elapsed years, months, weeks, and days. This tip shows how to arrive at such an answer using a relatively simple formula.

   Incrementing Months in Dates
Excel can easily store dates. If you want to increment a date by one month, there are a number of ways you can accomplish the task. Here are some ideas.

   Inserting Tomorrow's Date
You can use a couple of different worksheet functions to enter today's date in a cell. What if you want to calculate tomorrow's date or some other date a certain number of days in the future? It's easier than you might think to figure out that future date.

   ISO Week Numbers in Excel
Work in an industry that uses ISO standards when it comes to working with dates? You'll love the formula in this tip which explains how to determine the week number, of the year, for a given date.

   Leap Years and Fiscal Periods
Need to figure out when a fiscal year ends when that period does not correspond to the calendar year? Here are some ways that you can do the calculation and make sure you take leap years into account.

   Listing Dates at Regular Intervals
Need a way to enter dates for every other Tuesday (or some other regular interval)? Excel makes it easy, providing several different methods of entering the information.

   Month for the Nth Sunday
Doing math with dates is quite easy in Excel. As this tip illustrates, this fact makes it easy to figure out the Nth Sunday of any given year.

   Monthly Close-Out Dates
If your company closes out its accounting months at the end of each calendar quarter, figuring out the proper closing dates is a snap. It can be more challenging to figure out other closing dates, but it can be done by applying the techniques in this tip.

   Parsing Non-Standard Date Formats
When you load data into Excel that was created in other programs, the formatting used for some types of data (such as dates) may not be understood by Excel. Here's how to convert the data to a format that can be used by the program as you expect.

   Pulling All Fridays
It can be handy to know when specific weekdays occur within a range of dates. Figuring out this information, using formulas, can be tricky. Here's how to get just the dates you need.

   Pushing Dates Into Last Month
Excel is great when it comes to working with dates and times. You can even do math on dates. One such easy manipulation is to determine the last day of the month prior to a given date. This tip shows how.

   Returning Nothing If Two Values are Empty
Excel includes a large number of functions that can be used in evaluating the data in a worksheet. In this tip you learn various ways you can utilize those functions to determine if two input cells are empty.

   Rounding to the Nearest Quarter Hour
When entering times in a worksheet, you may have a need to round whatever you enter to the nearest 15-minute increment. There are a variety of ways you can approach this problem, as you learn in this tip.

   Sorting Dates and Times
One of the strong features of Excel is its ability to sort information in a worksheet. When it doesn't sort information as you expect, however, it can be confusing. Here's a look at why your dates and times might not sort the way you want them to.

   Specifying Different Weekends with NETWORKDAYS
The NETWORKDAYS worksheet function can be used to easily determine the number of work days (Monday through Friday) within a range of dates. If your workweek consists of different days, however, the NETWORKDAYS function may not be the best place to start.

   The Last Business Day
Many businesses need to know when the last business day of the month occurs. This tip discusses several ways you can calculate this date in Excel.

   Tombstone Date Math
Doing math with dates is easy in Excel. Doing math with old dates, such as those you routinely encounter in genealogy, is a different story. Here's how you can figure out birth or death dates given one or the other and a precise age.

   Unique Date Displays
Need to print an elapsed date in a strange format? It's easier to do than may appear at first glance. Here's a discussion on how to get your results in just the format you want.

   Unique Military Date Format
Some industries (such as the military) have special formatting that they use to represent dates. Here is one such format and how you can create the format in Excel.

   Using a Single Digit for a Year
Excel works very well with dates and times. One thing you cannot do, however, is to create a custom format that displays a single digit for a year. This tip looks at a couple of ways you can get around this limitation.

   Using a Text Function with a Date/Time Returns an Error
If you use a text function with a date or time, you'll get an error. To understand why this occurs (and how to get around it), you need to understand how Excel treats dates and times in a worksheet.

   Using a Two-Character Day of the Week in a Date Format
Excel provides quite a bit of flexibility in how you can format dates. Even so, some dates simply cannot be formatted through the use of custom formats. This tip looks at one such date format and provides a couple of ways you can still get the format you desire.

   Using Early Dates
Excel is brilliant at handling dates--as long as they aren't dates earlier than the base date used by the program. If you need to use earlier dates, then you'll want to be aware of the information in this tip.

   Weekdays in a Month
Want to find out how many of a particular weekday occurs within a given month? Here's how you can find the desired information.

   Years in Which a Date Occurred on a Particular Day
If you need to know the years in which a particular date occurred on a specific day of the week, there are a number of ways you can come up with your result. This tip looks at three different approaches you can use.