[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Date subtraction function in excel

Hi All
I am trying to develop some metrics on task aging.  I have a creation date for the task and a completion date.  The problems I am having are:
Where the completion date cell is empty (not completed) I get a [#VALUE!] error
Where the completion date cell has a date earlier than the creation date I get a [#NUM!] error.
This is the formula I am using: (Q4 is the creation date and V4 is the completion date)
Which works fine when completion cell is populated and the date is more recent than the creation date (Ok, you will ask why I have a completion date earlier than a creation date, simply data entry errors which I'll fix when flagged hopefully by a minus days value)
One other factor that I am not sure if it is influencing this, is that the data is extracted from an oracle dB as an slk file???
Any help ;-)
  • 3
  • 2
2 Solutions
Try this and format the cell with the formula as General.

ajeganAuthor Commented:
Thanks Imnorie, this works to reslove the [#NUM!] error but I still get a [#VALUE!] where the V4 cell is blank.
I can only get the #VALUE! error if I put ="" in either V4 or Q4.

Are either of the values in V4 or Q4 the results of formulas?

By the way, which version of Excel are you using?
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

barry houdiniCommented:
Try this version


regards, barry
ajeganAuthor Commented:
Really appreciate the quick responses guys, I'm away from the office now but will be right back at this in the morning (GMT here) and will let you know how I get on...

No, the values are not the result of formulas

Version is Excel 2007

I can work around this by adding additional columns and have the new cell populated with TODAY() if it is blank but I would really like to figure out a single cell formula.

Will get back with you tomorrow thanks again.....
ajeganAuthor Commented:
Needed to close the question...  apologioes for the really late closure

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now