Calculate date

Alexxu

Board Regular
Hello all.
In my workbook i want to calculate a date with certain periods.
My table look like this:
Code:
``````A                    B                                                             C
Current Date    Period between tests                                         Next Period
11.03.2009      (here i have to choose 1;3;6 or 12 months)                          =(A1+B1)
e.g 11.04.2009
or 11.07.2009,etc``````

My problem is:
1. I don't know how to write the formula from column C;
2. In the column C i wanted in the function to do the calculation, but i wanted to calculate that date minus 10 days.
e.g. instead of 11.07.2009 => 01.07.2009.
Many thanks!

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

VoG

Legend
Does this give the expected result?

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:75px;" /><col style="width:64px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Today</td><td >Months</td><td >Next date</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">11/03/2009</td><td style="text-align:right; ">3</td><td style="text-align:right; ">01/06/2009</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A2</td><td >=TODAY()</td></tr><tr><td >C2</td><td >=DATE(YEAR<span style=' color:008000; '>(A2)</span>,MONTH<span style=' color:008000; '>(A2)</span>+B2,DAY<span style=' color:008000; '>(A2)</span>-10)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Kelbo

Board Regular
or alternarively try:

=EDATE(A1,3)-10

Kelbo

Alexxu

Board Regular
Thanks Kelbo and Mr. Vog.
It seems that Mr. Vog's formula is PERFECT.
I have another small question.
Because one test takes place 1/week, if i try with days instead of monts, like:

1 month = 30 days;
3 months = 90 days;
6 months = 180 days;
12 months = 360 days;
1 week = 7 days.

How should i modify your formula?
Thanks!

VoG

Legend
Try

=DATE(YEAR(A2),MONTH(A2),DAY(A2)+B2-10)

Replies
3
Views
433
Replies
2
Views
160
Replies
3
Views
227
Replies
4
Views
358
Replies
2
Views
144

1,190,889
Messages
5,983,402
Members
439,843
Latest member
PlanetFitness

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back