calculating difference between d

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Calculating difference between dates

# Calculating difference between dates

Resolved · Low Priority · Version 365

Uday has attended:
Excel Intermediate course

### Calculating difference between dates

Hi,
I want to calculate the number of days between two dates, on a spreadsheet where some of the dates are missing.

so, for example, I am trying to work our how long we have had a number of projects ongoing for. Some projects have already completed, so I have a closure date, but some projects are still ongoing, so I want to use today's date (today function).

In this Scenario:
Column A has the project start date
Column B has the closure date (with some cells blank where the project is still ongoing)
Column C has today's date (using the today() function).
Column D is where i want to work out the total number of days a project has been ongoing for.

Is there a single formula I can use in column D which will populate the total number of days either by calculating the difference between column B minus column A or, where column B is blank, Column C minus column A?

(NOTE: the data is in a table, so I cannot have two different formulae in column D by selecting the blank cells of column B only)

### RE: calculating difference between dates

Hi Uday,

Thank you for the forum question.
Try in D2 (if your first record is in row 2)

=if(B2="",D2-A2,B2-A2)

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.