Date Formulas

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
If you need to figure out someone's actual age, in years and months, the technique can seem confusing. This tip examines several different format and formulaic approaches, paying attention to the drawbacks and advantages of each.

Alerts About Approaching Due Dates
If you keep a series of dates in a worksheet, you may need some way to highlight if those dates are within a certain range. This can be done in a couple of ways, as described in this tip.

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
Sometimes calculating values based on dates can be vexing. For instance, you might need to figure out how many months should be billed to a client based on starting and ending dates. This tip explores how you can figure out such vexing issues.

Calculating Months of Tenure
If you have an employee starting date, you can figure out how many months that employee has been working for your company. All you need to do is apply the DATEDIF function as described in this tip.

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

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.

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.

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.

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

Inserting Tomorrow's Date
If you need to add tomorrow's date to a worksheet, you can do it using simple math. The formulas provided in this tip are both illuminating and helpful.

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 the many ways you can calculate this date in Excel.

Unique Military Date Format
The military, for some purposes, uses a variation of a Julian date that is not directly supported by Excel. This tip explains various formulas you can use to start with a normal Excel date and display that date in the military's Julian format.