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

x
  • 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
and
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)
=DATEDIF(Q4,IF(V4=ISBLANK(V4),NOW(),V4),"d")
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 ;-)
0
ajegan
Asked:
ajegan
  • 3
  • 2
2 Solutions
 
NorieCommented:
Try this and format the cell with the formula as General.

=IF(V4=ISBLANK(V4),TODAY(),V4)-Q4
0
 
ajeganAuthor Commented:
Thanks Imnorie, this works to reslove the [#NUM!] error but I still get a [#VALUE!] where the V4 cell is blank.
0
 
NorieCommented:
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?
0
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

=IF(V4="",TODAY(),V4)-Q4

regards, barry
0
 
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.....
0
 
ajeganAuthor Commented:
Needed to close the question...  apologioes for the really late closure
0

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