Hi Everybody
I hope you are well!
I am trying to get a formula which states whether a contract is
1) In contract
2) Out of Contract
3) Within 6 months of renewal
4) Within 1 year of renewal
I have tried numerous IF formulas but I cannot seem to get it to work. There is a excel spreadsheet attached. Please feel free to give it a go.
Thanks
Mike
Dates formula.xlsx
Last edited by mfairhurst1; 09-22-2013 at 03:37 PM. Reason: rule 1
And how long are the contracts?
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Try this...
=IF(C5<TODAY(),"Out of Contract",
IF(C5>TODAY(),
IF(AND(C5>TODAY(),C5<=TODAY()+180),"6 Months to Renewal",
IF(AND(C5>TODAY(),C5<=TODAY()+365),"1 Year to Renewal","In Contract")),""))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
That works. thanks a lot for your help!!
Mike
you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 at the bottom of my post to this thread)
Or this:
=IFERROR(IF(DATEDIF(TODAY(),C5,"m")<=3,"In Countract",IF(DATEDIF(TODAY(),C5,"m")<=6,"6 Months to Renewal",IF(DATEDIF(TODAY(),C5,"m")<=12,"1 Year to Renewal"))),"Out of Contract")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks