|
A simple date calculation question.
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 |
A simple date calculation question.
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" a écrit dans le message de news: ... 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 |
A simple date calculation question.
Thanks for the answer.... -- Seldum ------------------------------------------------------------------------ Seldum's Profile: http://www.excelforum.com/member.php...o&userid=35793 View this thread: http://www.excelforum.com/showthread...hreadid=555594 |
A simple date calculation question.
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. -- Seldum ------------------------------------------------------------------------ Seldum's Profile: http://www.excelforum.com/member.php...o&userid=35793 View this thread: http://www.excelforum.com/showthread...hreadid=555594 |
A simple date calculation question.
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=555594 |
A simple date calculation question.
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. -- Seldum ------------------------------------------------------------------------ Seldum's Profile: http://www.excelforum.com/member.php...o&userid=35793 View this thread: http://www.excelforum.com/showthread...hreadid=555594 |
A simple date calculation question.
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=555594 |
A simple date calculation question.
Dav Wrote: 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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=555594 |
A simple date calculation question.
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 |
A simple date calculation question.
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 |
A simple date calculation question.
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 |
| All times are GMT +1. The time now is 01:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com