How to see if a particular cell in a table has date data or not

Solved/Closed
vinaymaithani Posts 4 Registration date Monday April 14, 2014 Status Member Last seen April 17, 2014 - Apr 14, 2014 at 04:19 AM
vinaymaithani Posts 4 Registration date Monday April 14, 2014 Status Member Last seen April 17, 2014 - Apr 16, 2014 at 12:51 AM
Hello,

I am preparing a tracking tool to measure the life of an item.

The table has
Installation No Date
1 01 Jan 2011
2 01 Mar 2012
3 01 April 2013
4 May or may not have data

I want to write a formula to calculate the Life of the item as of today from the last replacement, based on the last date entered in row no 3 or 4.

Like If Row No 3 has date data AND Row no 4 is Blank (means not replaced before this) then it should calculate the age from 01 April 2013 till Today's date.

Like if Row 4 has date data then it should calculate the life of the item from the date mentioned in Row No 4, Column of Date till today's date.

There are 20 such items with number of installation/replacement dates.

Hope this helps to clarify

Please help,

Regards
Vinay

2 replies

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Apr 14, 2014 at 11:35 AM
Hi Vinay,

Let's assume the dates are located in A2:A5.
=IF(A5="",TODAY()-A4,TODAY()-A5)

Best regards,
Trowa
1
Thanks Trowa for the response.
How about If the formula can be made suitable for having data in any of the cell like say...
If data is present say A2 and A3 and not in A4 and A5 Then how the formula should be.
If data is present A2, and not in others A3, A4 and A5.

I tried nested IF but I messed it up every time.

Please help.

Regards
1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Apr 15, 2014 at 10:20 AM
Hi Vinay,

Then the formula would look like:
=IF(A2="","",IF(A3="",TODAY()-A2,IF(A4="",TODAY()-A3,IF(A5="",TODAY()-A4,TODAY()-A5))))

Best regards,
Trowa
0
vinaymaithani Posts 4 Registration date Monday April 14, 2014 Status Member Last seen April 17, 2014
Apr 16, 2014 at 12:51 AM
Thank you Trowa D.
You are the Guru for me now.

You are genius. May God shower with more time to live & help people and enjoy what you wanna do in life.

Cheers.
Vinay
0