Expiration Date

[stevenchaplin]

I am putting together a spreadsheet, and i want it to give me clearance expiration dates. Column D shows eligibility, and G shows a close date. I want it to populate the expiration date in column I. If D shows S then it needs to be ten years from the date in G. If D shows SCI then it needs to show five years from the date in G. The date format i am using is for example 23-Apr-13. I am using excell 2007. I apologize in advance if this is already covered or if i did not provide enough information. I am new, be easy on me.

[Lemice]

Hello,

You can use the DATE formula like this

Please Login or Register  to view this content.

Assuming D1 = SCI or S, and the date is in G1, you can paste that formula in I1

[bebo021999]

What if D differ from "S" or "SCI"? I suppose the expiration date does not change in that case.
Try this:
=EDATE(G1,IF(D1="S",120,IF(D1="SCI",60,0)))

[stevenchaplin]

When i do this it populates a number at the end. Thats what is confusing me.

[Lemice]

If you see a number, that means it's working. Please format it as "Date" format, not "Gereral", which can be done through Format --> Cells

[bebo021999]

try to format the cell as date.(Ctrl-1)

Untitled.jpg

[stevenchaplin]

Yeah, I told yall i was new. It works i greatly appreciate it. Do i have to input the formula in every row individually, or is there something i can do to make it do it for me

[Lemice]

You can copy paste it in every row, or you can simply "drag" it - when you select the cell, there is a big dot at the lower right corner, you can simply click it, hold it and drag it down to as many cell as you want. The other way is copy paste - copy the cell with formula, select all cells you want to apply the formula, then paste it (I assume they are all in a column / row).

[stevenchaplin]

Alright, now my boss thinks im a genius so he wants me to take it a step further. If we are a year out he wants it to populate green, if we are six months yellow, and if we are a month out red.

[Lemice]

You can do it all with conditional format. Can you provide a small sample file?

[stevenchaplin]

Capture.JPG

The shaded in area is what i want to change colors

[Lemice]

I know you wanted to change color, but I was hoping for a small sample excel file so I can put in a condition formal, and all you have to do is copy paste special that format into your file ...
On the formula, it will only add 5 or 10 years, so of course it will be Green on everything I guess?
by the way, you should change the "" at the end of the formula to G1, so it changes to this

Please Login or Register  to view this content.

In case, like bebo said, nothing is put in D1 (If nothing is put in D1, my formula will return "Blank" instead).
Also, I suggest using his formula too, really simple and straight forward, isn't as complicated-look-alike like mine.

[stevenchaplin]

Sorry i cant give an actual file its protected information.

[Lemice]

You don't have to give the entire file, just couple of rows of examples is fine, see the attached file.
What you can do is copy the cell, right click on the cell on your actual workbook, "paste special...", choose "format".
Or just copy paste the entire cell and paste accordingly lol