Topic: Date Formulas

The following articles are available. 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Calculating the Day of the Year Excel allows you to perform math with date values. That means it is easy (as shown in this tip) to figure out what day of the year it is and how many days are left in the year. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Every Second Tuesday There are multiple ways that you can generate a list of every other Tuesday. This tip explores several of the fastest and easiest ways to do it—all without macros! Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Forcing Dates Forward Do you need a date to always default to a particular day in the month? The formulas presented in this tip allow you to force a date to the next occurrence of a particular day in a month, and if the date is after the target date, then to that date in the following month. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Tips, Tricks, and Answers

Confused by Time?

Helpful Links

Great Info!

RSS Feeds