A simple date calculation question.

[Seldum]

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]

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

HTH

 

[Guest]

Much neater than my attempt in duplicate post!
Sheila

 

[Seldum]

Thanks for the answer...

 

[Seldum]

Well it works, sorta.

The thing is, I have 14.349 contracts to calculate the end date on, an
typing in the year, month, day manually into the formul
=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]

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]

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]

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

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

Da

 

[daddylonglegs]

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

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 dat

 

[Guest]

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

 

[Seldum]

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.

 

[Guest]

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 ,