I have a spreadsheet that has Date of Report (column H), Date of Assignment (column J), and Close Date (column I).
I need a function that will do the following and place value in column M.
IF I=0 and J=0 then M= Today-H
IF I=0 and J>0 then M= Today-J
IF I>0 and J>0 then M= I-J
IF I>0 and J=0 then M= I-H
Try this in cell M1
Formula:
Please Login or Register to view this content.
Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.
Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
If you're happy with an answer given, please click the * under the person's name to boost their reputation.
=IF(and(I2=0,J2=0),now()-H2,if(and(I2=0,J2>0),now()-J2,if(and(I2>0,J2>0),I2-J2,if(and(I2>0,J2=0),I2-H2))))
Formula:Please Login or Register to view this content.
Or, more specifically (and working):
Formula:Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
@daffodil
As now() returns date and actual time, it might slow down calculations due to it's volatility,and produce false results ( don't know if it's the case here)
Fi now()-J2 is larger than today()-J2. Personally I would suggest TODAY() (as OP only seems to need dates)
That worked. Thanks! I had the right idea but was making it waaay too difficult.
I never even heard of Today()! This is great!
@Pepe: TODAY() is volatile too
@daffodil11: if you compare TODAY() and NOW() you will not get a match. Equally, if the OP compares his dates to NOW() he's not going to match them either.
Regards, TMS
Did I imply somewhere that it wasn't ? If so,as I already said, please excuse my poor command of the English languageOriginally Posted by TMShucks
Last edited by Pepe Le Mokko; 07-23-2013 at 02:26 PM.
Maybe not. Perhaps just my interpretation of the sentence:Did I imply somewhere that it wasn't ?
And then:As now() returns date and actual time, it might slow down calculations due to it's volatility
Personally I would suggest TODAY()
Hence, volatility is not a factor as both would cause a re-calculation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks