Dates in vba code

**grk_chakri** · 02-14-2012, 03:33 PM

Hi All

I am trying to write a code for a cell on sheet 1 where the user inputs the value of date and in the next column or in the next sheet it should 6 months less 1 day. for example if user enters 01/01/2012 the result should be 30/06/2012 instead of exactly 01/07/2012 . I have tried the code below. The below code works for me to show exactly 6 months. But when i use "d" Instead of "m" some times the date values are incorrect. I Think thats because some have 30 days and others have 31 days. how to correct the code below so that it gives always 1 day less than 6months.

Many thanx

 

D28 = 01/01/2012

If Range("D24").Value = "FIXED" Then

Range("D29").Value = DateAdd("m", 6, Range("d28").Value)

else

Range("D29").Value = "XXXX"

End If

**Swindon Dominic** · 02-14-2012, 05:54 PM

Hi
There would seem to be a logic problem in what you are trying to do. If you add 6 months to 31 August you get a date that does not exist. I think you need to define your logic so that it works for all dates and then you can write the code to implement the logic.

**OnErrorGoto0** · 02-14-2012, 07:23 PM

Range("D29").Value = DateAdd("m", 6, Range("d28").Value) - 1

may suffice for your purposes.

**grk_chakri** · 02-15-2012, 12:24 AM

Such a foolish mistake it was, Any ways thanx a ton my dear friend,

**royUK** · 02-15-2012, 01:23 AM

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save