Reputation: 19
IF statement formulas
Need assistance with creating a formula to calculate how many days of storage. The attached image of a spreadsheet that I use shows an array of dates.
I need a formula in column "E" "Live Days in SIT" that calculates the following:
1st: If column D & C have dates, subtract column D from C
2nd: If all columns have date, subtract column D from C
3rd: If column C is only cell with dates, subtract Todays date from column C
4th: If column C & B have dates, then subtract B from C
5th: If column C & A have dates, then subtract A from C
6th: If column A, B, C have dates, subtract the lesser dates from columns A & B from C
This is the formula I have created, but I receive a "False" statement when only column C has a date:
=IF(L4758=0,IF(J4758=0,IF(I4758=0,TODAY()-K4758)),IF(L4758>0,L4758-K4758,IF(L4758=0,IF(I4758=0,IF(J4758>0,J4758-K4758,IF(J4758<I4758,IF(J4758>0,J4758-K4758,IF(J4758>I4758,IF(I4758>0,I4758-K4758)))))))))
Thank you!
Upvotes: 0
Views: 53
Answers (2)
Reputation: 2272
This should work, but I got a little confused as to which column should be subtracting from which. I followed your verbal commands, but my intuition says you probably are looking for a different method than the one you described. Here it is, you can adjust if need be.
=IF(AND(C6<>"",D6<>""),C6-D6,IF(AND(D6<>"",C6<>"",B6<>"",A6<>""),C6-D6,IF(AND(D6="",C6<>"",B6="",A6=""),C6-TODAY(),IF(AND(C6<>"",B6<>""),C6-B6,IF(AND(C6<>"",A6<>""),C6-A6,IF(AND(A6<>"",B6<>"",C6<>""),IF(A6>B6,C6-B6,C6-A6)))))))
Upvotes: 1
Reputation:
Try,
=if(count(a2:d2)=4, c2-d2, if(count(a2:c2)=3, c2-min(a2:b2), if(and(count(a2:d2)=2, c2<>0), c2-sum(a2, b2, d2), text(,))))
There are still some 'holes' in your logic but I suppose those are conditions that will never be met.
Upvotes: 1