help with formulate refers to values

meninio · #1 01-26-2011, 04:38 AM

Guys, Good day.

I have a question regarding a formulate. I need to know how many months are left in a contract, for example:
I have the start date (1/11/2011) and termination of a contract (31/12/2011) and based on what we are (26/01/2011) want to know how many months left to end the contract.

I figured it would be like = SUM (contract expires) - (date (today)) or = MONTH (Today) - CONTRACT TERMINATION

Could someone explain to me how it works?

Thanks

Colin Legg · #2 01-26-2011, 05:21 AM

Hi,

The formula will be very similar to the excellent answer ConneXionLost gave you on this thread:
https://www.msofficeforums.com/excel...el-2010-a.html

The only extra piece you need is how to return today's date. You can do this using the TODAY() function.

Does that help?

meninio · #3 01-26-2011, 05:45 AM

It would be like this:

DATEDIF = (TODAY (), 'end date)

?

Colin Legg · #4 01-26-2011, 06:01 AM

Almost. Please see attached example, based on the spreadsheet that was attached on the other thread.

Code:

=IF(B2>C2,
    DATEDIF(C2,B2,"m")&" months "&DATEDIF(C2,B2,"md")&" days",
    "0 days")

meninio · #5 01-26-2011, 06:10 AM

Okay, I do not have the option today in my spreadsheet, where the choice today should be on the same formulate. Is it possible?

In Open Office works as follows:

= MONTH (TODAY ();$' END DATE '. $ G $ 7, 0) and that the G7 is where is located the end date. This way the update is automatic and I do not need a field today () on a new cell.

Colin Legg · #6 01-26-2011, 06:15 AM

Hi,

Sure, you can - just replace C2 in the formula with TODAY()

Code:

=IF(B2>TODAY(),
    DATEDIF(TODAY(),B2,"m")&" months "&DATEDIF(TODAY() ,B2,"md")&" days",
    "0 days")

However, I would recommend that you spare one cell in your worksheet for the TODAY() function and reference it from the formulas because:
1. This will give you more flexibility. For example, if you quickly wanted to see time remaining on contracts for a date other than today, you could quickly change the value in that one cell to see the results.
2. TODAY() is a volatile function. It is more efficient to structure the sheet this way.

Hope that helps...

meninio · #7 01-26-2011, 06:16 AM

Resolved:
DATEDIF = (TODAY () DATE END; "M")

Thanks for help

meninio · #8 01-26-2011, 06:25 AM

I forgot one point if today's date is later than the end date. How to make up the negative?

Colin Legg · #9 01-26-2011, 06:36 AM

DATEDIF() can't handle an end date earlier than the start date, so I think you would have to arrange the formula like this:

Code:

=IF(B2<TODAY(),"- ","")&
    DATEDIF(MIN(B2,TODAY()),MAX(B2,TODAY()),"m")&" months "&
    DATEDIF(MIN(B2,TODAY()),MAX(B2,TODAY()),"md")&" days"

meninio · #10 01-26-2011, 08:43 AM

Good Afternoon,

Could be made available in a form in excel? I tried to assemble the formulate but unsuccessfully.

Colin Legg · #11 01-26-2011, 09:47 AM

Sure, please see attached.

meninio · #12 01-26-2011, 10:00 AM

Colin Legg,

Thanks for help!

Problem is resolved.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formulate month in Excel 2010	meninio	Excel	4	01-25-2011 12:13 PM
Drag Button which refers to the active Word document to Explorer	DRob	Word	0	10-03-2010 06:40 AM
Using Conditions to Add Values	EclipticalD	Excel	1	07-08-2010 09:50 PM
Opening .pst with dummy values	ran_sushmi	Outlook	0	08-19-2009 01:56 AM
Fields controlled by List box values	farfromapro	Word	0	02-11-2009 02:19 PM