DateAdd

[moi]

Hello all,

I have this dateadd function, i wanted to add a year (2/3yrs) depending on the value of "terms" and "ctsreleased" field..
=DateAdd("m",[terms],[ctsreleased])-1

Like i have a date "30-Jul-2024", i want to give a me date "29-Jun-2027".
Thank you.

 

[plog]

What do you specifically need help with?
Why are you posting and not coding?
29-Jun-2027 isn't exactly 2 or 3 years after 30-Jul-2024, why's it short a day and a month?
How can we help you exactly, what issue are you having?

 

[GaP42]

The function dateadd is DateAdd ( interval, number, date ).
If you want 2/3 years added to a date then number = 8, when interval is "m".
Is [Terms] a value or string or date?
Do you want the last day of the month in all cases?

 

[moi]

What do you specifically need help with?
Why are you posting and not coding?
29-Jun-2027 isn't exactly 2 or 3 years after 30-Jul-2024, why's it short a day and a month?
How can we help you exactly, what issue are you having?

Hi plog.. Many thanks for picking up my post.. Thats what they calculated for 3 years from 30-jul-2024, so i need to follow that calculation..

 

[tvanstiphout]

The delta between "30-Jul-2024" and "29-Jun-2027" is 3 years minus 1 month minus 1 day. Is that REALLY what you meant, or is there a typo?

 

[moi]

The function dateadd is DateAdd ( interval, number, date ).
If you want 2/3 years added to a date then number = 8, when interval is "m".
Is [Terms] a value or string or date?
Do you want the last day of the month in all cases?

Hi GaP42,

Terms is value (24/36)..No it is not always end of the month.

 

[moi]

The delta between "30-Jul-2024" and "29-Jun-2027" is 3 years minus 1 month minus 1 day. Is that REALLY what you meant, or is there a typo?

Hi, yes exactly..

 

[plog]

What have you tried and how is it not working?

 

[The_Doc_Man]

The obvious way to do that for the "number of years, less 1 day" case is with

TargetDate = DateAdd( "yyyy", n, [StartingDate] ) - 1

The problem with variables as intervals is that nothing can be input as fractional values because the input period is an integer number and the input interval has a limited range of choices. So putting [terms] in the function (based on what you showed in post #1) limits what [terms] can be.

You are fortunate that base 12 numbers have lots of factors, so you can do 1/12, 1/6, 1/4, 1/3, and1/2 as year-fractions easily. But to know what would be proper here, we would need to know maybe a little more of your potential goals. What would be typical intervals for this computation?

 

[Gasman]

Why are you using m (months) when you want to add years (yyyy) ?

 

[ebs17]

DateSerial is wonderfully variable.

? #2024-07-30#, DateSerial(2024, 7 + 35, 30 - 1), DateSerial(2024 + 3, 7 - 1, 30 - 1)
30.07.2024    29.06.2027    29.06.2027

 

[CJ_London]

I think OP is confusing the issue - terms are 24 or 36 months which has been translated as 2 or 3 years. Requirement is one month less than 24/36 months (presumably for a reminder letter or similar) so all that is required is to add terms minus 1 - ie 23 or 35 months

 

[moi]

What have you tried and how is it not working?

As of now, what i have is the same i posted..

 

[moi]

The obvious way to do that for the "number of years, less 1 day" case is with

TargetDate = DateAdd( "yyyy", n, [StartingDate] ) - 1

The problem with variables as intervals is that nothing can be input as fractional values because the input period is an integer number and the input interval has a limited range of choices. So putting [terms] in the function (based on what you showed in post #1) limits what [terms] can be.

You are fortunate that base 12 numbers have lots of factors, so you can do 1/12, 1/6, 1/4, 1/3, and1/2 as year-fractions easily. But to know what would be proper here, we would need to know maybe a little more of your potential goals. What would be typical intervals for this computation?

Hello The-Doc-Man,

What is their typical intervals are 2years (24mos), 3years (36mos).. They are using 24/36 mos.. No other intervals they are using..

 

[moi]

Ye

I think OP is confusing the issue - terms are 24 or 36 months which has been translated as 2 or 3 years. Requirement is one month less than 24/36 months (presumably for a reminder letter or similar) so all that is required is to add terms minus 1 - ie 23 or 35 months

Yes that is what they wanted.. I need to follow what they are using..

 

[moi]

Why are you using m (months) when you want to add years (yyyy) ?

They are using number of months 24, 36..

 

[Gasman]

I would have thought it was add whatever months/years less 1 day?

 

[CJ_London]

that is what they wanted..

So

dateadd(“m”,[terms]-1, [ctsreleased])

 

[moi]

I would have thought it was add whatever months/years less 1 day?

Thank you gasman..

 

[moi]

So

dateadd(“m”,[terms]-1, [ctsreleased])

Thank you cj_london