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.

   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.

   Alerts About Approaching Due Dates
You may use Excel to track due dates for a variety of purposes. As a due date approaches, you may want that fact drawn to your attention, in some fashion. This tip examines different approaches you can take to getting the alert.

   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 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 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 Day of the Year
Need to know what day of the year a certain date is? You can figure it out easily using the formulas in this tip.

   Calculating the First Business Day of the Month
Want to know which day of the month is the first business day? There are a few ways you can check to make sure the date doesn't fall on a weekend, but the easiest way to determine the date is to use the WORKDAY function.

   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.

   Converting Between Buddhist and Gregorian Calendar Systems
Converting from one calendar system to another can be a challenge. The key is identifying the differences between the systems and then creating a formula to adjust for those differences.

   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 "04A09". This tip explains how you can convert such strange formats into something you can use.

   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.

   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 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.

   Every Second Tuesday
Need a way to enter dates from every second Tuesday (or some other regular interval)? Excel makes it easy, providing several different methods of entering the information.

   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.

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

   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.

   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.

   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.

   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.