How to see if a particular cell in a table has date data or not [Solved/Closed]

Report
Posts
4
Registration date
Monday April 14, 2014
Status
Member
Last seen
April 17, 2014
-
Posts
4
Registration date
Monday April 14, 2014
Status
Member
Last seen
April 17, 2014
-
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

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Vinay,

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

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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
Posts
4
Registration date
Monday April 14, 2014
Status
Member
Last seen
April 17, 2014

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