date calculations

**grkchakri** · 05-06-2013, 03:25 AM

Hi All

I am trying to write a vba code for calcuating dates. While the details are as follows

A1 : 01-05-2013
B1: 2 (2 being the number of years)
C1 : I have inserted a date formula : DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)-1)

So that in C1 i can have 30 -04-2015 which is 1 day less than the Exactly two years. While i try to write the same at VBA level it doesnt work.

Please Login or Register  to view this content.

Help Required please. I have an exam tomrrow

**AndyLitch** · 05-06-2013, 08:49 AM

The basic format/syntax is

Range("G6").FormulaR1C1 = "=DATE(YEAR(R[-5]C[-6])+R[-5]C[-5],MONTH(R[-5]C[-6]),DAY(R[-5]C[-6])-1)"

Where the R[] and C[] values are references to cells relative to the target cell.

**grkchakri** · 05-06-2013, 09:16 AM

If my understanding is write with the regards the above code

in G6 It will calculate the date given ROW 5 AND COLUMN 6 WHICH IS F5 , AM I RIGHT.
But still doesnt seem when i am trying to input the code as . what is the incorrect thing in the below code

Please Login or Register  to view this content.

Many thanx for your help.

**ragavan.sridar1** · 05-06-2013, 09:28 AM

NO,

it is -5 rows from G6, "-" indicates the backward row.

so here it refers to a1.

Give the formula in your module code give it a try..

**Norie** · 05-06-2013, 09:47 AM

Change Date to DateSerial.

Please Login or Register  to view this content.

**grkchakri** · 05-06-2013, 10:36 AM

See below the comments updated by Norie and its working for me. Thats is simpler and easier to understand. But Thankx for your help and giving me your inputs.