Create an Excel Formula that Calculates Time Between 2 Dates

This post will show you how to create a formula that calculates times between 2 dates. The pattern for dates in Excel are as follows:

  • Jan 1, 1900 = 1
  • Jan 2, 1900 = 2
  • Jan 3, 1900 = 3
  • Nov 11, 2013 = 41,589

So understanding that Excel has a number assigned for a date, you can subtract 2 dates to calculate the difference. Once you subtract the past from the future date, divide by 365 to break it down by years.

There is a function =TODAY() that updates to the current date when the file is refreshed or opened. The formula =(TODAY()-B2)/365.25. You noticed the 365.25.🙂 That .25 is for you nerds out there that want to take leap year into the formula.

Excel years of service formula

Once you get the values, format the cells with fewer decimals.

Excel formulas with dates

Download my Excel keyboard shortcuts here.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel. Bookmark the permalink.

3 Responses to Create an Excel Formula that Calculates Time Between 2 Dates

  1. lbrooks687 says:

    is there away to make a excel spreadsheet that to show dates for commercial drivers physical that would be due every 2 years and some 1 year, and when the drivers license would be due; and alert or color change? I track 42 commercial drivers and I must notifie them at least 2 to 3 weeks prior to the due dates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s