ExcelTip.com
ExcelTip.com
Account Icon Account Icon Account Icon
Google Exceltip.com
JOIN OUR NEWSLETTER
  and receive for joining:
Free eBook Learn More!
Free Excel ADD-IN
Free Weekly Excel Tip
4 e-books in cd-rom
F1 Formulas & Functions
F1 Formulas & Functions
F1 EXCEL
F1 eBook (Spanish)
F1 EXCEL
Financial Statements.xls
 

» Eliminating Negative Values from Date Subtractions


Problem:

Creating a formula to return the number of days between each date in column A and the corresponding deadline in column B.
In cases where the date in column A is later than the deadline, the formula is to return zero rather than a negative number of days.

Solution:

Use the MAX function as shown in the following formula:
=MAX(B2-A2,0)
Rate this tip
12 34 5
  RATING: 3.50
  VIEWS: 7364

READER COMMENTS (view all comments)


Very Good Formula
Mhz wrote on December 31, 1969 19:00 EST
Thanks for this formula it works great for counting past days of the current date. The only problem is that you get a 39015 value for any blank cells... How is this fixed? Thanks In Advance



REGISTERED USERS - Click here to post comments


GUESTS (Click here to register)
Name
Comment Title
Comments

Microsoft Access 2002 for Dummies

Special Edition Using Microsoft Outlook 2002

What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade

Special Edition Using Microsoft Access 2002

H&R Block's Just Plain Smart(tm) Tax Planning Advisor: A year-round approach to lowering your taxes this year, next year and beyond

Accounting and Financial Fundamentals for Nonfinancial Executives

RELATED MICROSOFT EXCEL TIPS


Convert PDF Files to Excel


Excel VBA books
Accounting books
Business Plans
MS Office books
Taxes books

VIEW ALL BOOKS