In column I, I have a list of dates and comments regarding a shipping schedule. I have a formula that calculates my dates but if there is a comment I want the value of just the comments in column J. I've attached an example of what I'm working with and what I want.
why is J2 31-7-2017?
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
In my post I stated that "I have a formula that calculates my dates but ..."Originally Posted by oeldere
That doesn't actually answer the question..
How does J2 become 7/31/2017. because that value doesn't exist in I2
=iferror(if($j2="vsl",edate($k2+7,1),$k2+7)
Please Login or Register to view this content.
Ben Van Johnson
Thanks this works... Question though. What does "D4" represent in this formula?Please Login or Register to view this content.
The CELL() function returns information about a cell. In this case, it is looking at I2, and it returns "D4" because the data in I2 is:
m/d/yy or m/d/yy h:mm or mm/dd/yy , a standard date.
That's not quite correct.
It returns D4 because the cell is 'formatted' as a date. The data in the cell can be anything.
I can write "Hello, My name is Jon" in I2, and the Cell function would still return "D4"
Then the I2+7 would be a #Value! error.
Although it is a clever formula.
Verifying if a cell contains an actual date is next to impossible with Excel Formula.
But that Cell function takes us one step closer.
Before we could do =ISNUMBER(I2)
But that only verifies it's numeric, and dates are numeric. But isnumber is true for ANY number, not necessarily the numeric value of a date.
The combination of Isnumber with that Cell function in an And is probably as close as we can get.
AND(ISNUMBER(I2),CELL("format",I2))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks