A simple date calculation question.

**Seldum** · 06-26-2006, 10:25 AM

Heres my problem. I want to calculate the end date of a contract.

I have the start date fx. 30.05.06 (date field) and I know the contract runs for a periode of fx. 40 month (not a date field) - how do I get the end date?

I have looked in the forum and coulden't find an answer. The dates can variate at the beginning date fx. 10.05.06 but it will always end at the date when the contract runs out fx. 10.10.06 if the periode = 5 (month).

Is this possible to calculate?

Thanks.

Ardus Petus · 06-26-2006, 10:55 AM

Say A1 holds start date, and B1 holds # of months
=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))

HTH
--
AP

"Seldum" <[email protected]> a écrit dans
le message de news: [email protected]...
>
> Heres my problem. I want to calculate the end date of a contract.
>
> I have the start date fx. 30.05.06 (date field) and I know the contract
> runs for a periode of fx. 40 month (not a date field) - how do I get the
> end date?
>
> I have looked in the forum and coulden't find an answer. The dates can
> variate at the beginning date fx. 10.05.06 but it will always end at
> the date when the contract runs out fx. 10.10.06 if the periode = 5
> (month).
>
> Is this possible to calculate?
>
> Thanks.
>
>
> --
> Seldum
> ------------------------------------------------------------------------
> Seldum's Profile:
> http://www.excelforum.com/member.php...o&userid=35793
> View this thread: http://www.excelforum.com/showthread...hreadid=555594
>

Sheila D · 06-26-2006, 12:30 PM

Much neater than my attempt in duplicate post!
Sheila

"Ardus Petus" wrote:

> Say A1 holds start date, and B1 holds # of months
> =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))
>
> HTH
> --
> AP
>
> "Seldum" <[email protected]> a Ã©crit dans
> le message de news: [email protected]...
> >
> > Heres my problem. I want to calculate the end date of a contract.
> >
> > I have the start date fx. 30.05.06 (date field) and I know the contract
> > runs for a periode of fx. 40 month (not a date field) - how do I get the
> > end date?
> >
> > I have looked in the forum and coulden't find an answer. The dates can
> > variate at the beginning date fx. 10.05.06 but it will always end at
> > the date when the contract runs out fx. 10.10.06 if the periode = 5
> > (month).
> >
> > Is this possible to calculate?
> >
> > Thanks.
> >
> >
> > --
> > Seldum
> > ------------------------------------------------------------------------
> > Seldum's Profile:
> > http://www.excelforum.com/member.php...o&userid=35793
> > View this thread: http://www.excelforum.com/showthread...hreadid=555594
> >
>
>
>

**Seldum** · 06-26-2006, 04:15 PM

Thanks for the answer....

**Seldum** · 06-27-2006, 02:51 AM

Well it works, sorta.

The thing is, I have 14.349 contracts to calculate the end date on, and typing in the year, month, day manually into the formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) would take me forever.

How can I split up the date field into year, month and day?

If I put in the whole date in the 3 fields in the formula it wont work.

**Dav** · 06-27-2006, 04:17 AM

It is assumed that your date field is in cell A1 and the period is in cell b1, and possibly the result of the formula would be in c1, if not you will have to change these, but ifthey are in a list so the next contract date is a2 and period b2. You can copy the cells down and the formulas will change to look at the appropraite cells

Regards

Dav

**Seldum** · 06-27-2006, 04:50 AM

Well I can't make it work.

The date field/cell A1 is like this: 01.10.06 - date cell
The periode field/cell is like this: 40 - number

In my excel it wants the formula like this:

=DATE(YEAR;MONTH;DAY)

If I add the year = 2006, month = 01 and day = 10 manually and adds the cell B1 = 40 it so the formula looks like this; =date(2006;01+B1;10) it works perfectly.

But I cant make it automatic where the formula reference is like this:

=date(A1;A1+B1;A1)

I think that this is what excel see when I try to calculate the end date using the above formula:

=date(01.10.06;01.10.06+40;01.10.06) which ofcause won't work.

I hope you guys understands what I mean. And thanks for all the efford.

**Dav** · 06-27-2006, 08:05 AM

If your dates as 01.10.06 are treated as text strings they will not work in the formulas given above, as the default dateformat is with "/" not "."

try
DATE(YEAR(DATEVALUE(SUBSTITUTE(a1,".","/"))),MONTH(DATEVALUE(SUBSTITUTE(a1,".","/")))+B1,DAY(DATEVALUE(SUBSTITUTE(a1,".","/"))))

This will convert the date into a format it will recognise with / instead of .

Are you any closer!

Regards

Dav

**daddylonglegs** · 06-27-2006, 08:23 AM

Originally Posted by Dav

If your dates as 01.10.06 are treated as text strings they will not work in the formulas given above, as the default dateformat is with "/" not "."

try
DATE(YEAR(DATEVALUE(SUBSTITUTE(a1,".","/"))),MONTH(DATEVALUE(SUBSTITUTE(a1,".","/")))+B1,DAY(DATEVALUE(SUBSTITUTE(a1,".","/"))))

This will convert the date into a format it will recognise with / instead of .

Are you any closer!

Regards

Dav

To convert such a date this would suffice

=SUBSTITUTE(A1,".","/")+0

format as date

Beat · 06-29-2006, 03:50 AM

Try to understand "Ardus Petus" answer! This is the way to go !

"Seldum" wrote:

>
> Heres my problem. I want to calculate the end date of a contract.
>
> I have the start date fx. 30.05.06 (date field) and I know the contract
> runs for a periode of fx. 40 month (not a date field) - how do I get the
> end date?
>
> I have looked in the forum and coulden't find an answer. The dates can
> variate at the beginning date fx. 10.05.06 but it will always end at
> the date when the contract runs out fx. 10.10.06 if the periode = 5
> (month).
>
> Is this possible to calculate?
>
> Thanks.
>
>
> --
> Seldum
> ------------------------------------------------------------------------
> Seldum's Profile: http://www.excelforum.com/member.php...o&userid=35793
> View this thread: http://www.excelforum.com/showthread...hreadid=555594
>
>

**Seldum** · 06-29-2006, 05:31 AM

Well the date cell is formattet as a date cell.

Heres how I solved the problem:

Start date, a1: 01.01.06 - date field/cell
Periode, b1: 40 (month)

Question: How do I calculate the end date?

Well I almost got the excact day +/-2
Answer, c1: = (365/12)*b1+a1

I almost got the excact date using this rather simple formula. Its not 100% precise but in this case its good enough.

Beat · 06-29-2006, 05:50 AM

If you have Date (what ever format) in A1, number of months in B1? then copy
past the exact line below in C1:
=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))
Maybe you have to adjust by 1 day (1.1.2006...31.12.2006; not 1.1.2007)
then copy past
=DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)-1)
If you use European XLS format: separate the parameters in DATE with ; not ,

"Seldum" wrote:

>
> Well the date cell is formattet as a date cell.
>
> Heres how I solved the problem:
>
> -Start date, a1: 01.01.06 - date field/cell
> Periode, b1: 40 (month)-
>
> QUESTION: HOW DO I CALCULATE THE END DATE?
>
> Well I almost got the excact day +/-2
> Answer, c1: = (365/12)*b1+a1
>
> I almost got the excact date using this rather simple formula. Its not
> 100% precise but in this case its good enough.
>
>
> --
> Seldum
> ------------------------------------------------------------------------
> Seldum's Profile: http://www.excelforum.com/member.php...o&userid=35793
> View this thread: http://www.excelforum.com/showthread...hreadid=555594
>
>