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.

Advertisements

About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant. Please contact me on my new website at www.sequentiasolutions.com
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:

WordPress.com Logo

You are commenting using your WordPress.com 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