Adding a year and a half to a giving date

[Enigma20xx]

I'm trying to add greater than a year periods to a starting date.

I use a calc field, result date:

Date ( Month ( startdate ) ; Day ( startdate ) ; Year ( startdate ) + Period ).

Works fine if years are 1, 2, 3... and so on. But what if the period is 1.5 years or 3.5.

What should I do?

Thanks.

[Enigma20xx]

Self answer .

By now I have solved it like this.

Date ( Month ( startdate ) + Case ( Right ( Period ; 2 ) = .5 ; 6 ; "" ) ; Day ( startdate ) ; Year ( startdate ) + Period ).

But this only gives me the next one up.

[eos]

Try this one:

let (



[ startdate = Get ( CurrentDate ) ;



period = 3,5 ;



monthPeriod = 12 * ( period - Int ( Period ) ) ;



daysPeriod = 30 * ( MonthPeriod - Int ( MonthPeriod ) ) ] ;



Date ( Month ( startdate ) + MonthPeriod ; Day ( startdate ) + daysPeriod ; Year ( startdate ) + Period )



)

This is only an example, so replace startdate and period with your values

It seems the Date function only uses integers to work its magic, so you need to take the decimal part

and calculate it as month value.

Note that months are Base 12 while Period will be given in Base 10, so unless you specify .5, you'll end up

with a value to add to the Month parameter that also has a decimal part, which will also be ignored.

You can take that one and calculate days from it, but this will be less precise, since not each month has

the same number of days (while each year has 12 months).

[E.J. Sexton]

What about this:

Let([

startdate=Get(CurrentDate);

period=3.5

];

Date (Month(startdate) + 12*period; Day(startdate); Year(startdate))

)

[Enigma20xx]

Thank you eos and E.J. Sexton.

Taking note of your sugestions I have improved mine, but still are a few errors.

eos:

Let ( [

StartDate = StartDate ;

Period = Period ;

MonthPeriod = 12 * ( Period - Int ( Period ) ) ;

DaysPeriod = 30 * ( MonthPeriod - Int ( MonthPeriod ) )

] ;

Date ( Month ( StartDate ) + MonthPeriod ; Day ( StartDate ) + DaysPeriod ; Year ( StartDate ) + Period )

)

I don't mind days, first of month is enough for me, and this function counts days too and sometimes (years with february 29) can loose or gain a month.

E.J. Sexton:

Let ( [

StartDate = StartDate ;

Period = Period ;

] ;

Date ( Month ( StartDate ) + 12 * Period ; Day ( StartDate ) ; Year ( StartDate ) )

)

Mine:

Let (

[ MonthPeriod = 12 * ( Period - Int ( Period) ) ] ;

Date ( Month ( StartDate ) + MonthPeriod ; Day ( StartDate ) ; Year ( StartDate ) + Int ( MonthPeriod ) )

)

All of them apparently work but have the same error. When the period is .5 it works like if it were half a year, that is adds 6 months, not 5 months so the result goes wrong.

I.e.:

StartDate: 06/01/2012

Period: 1.5 (counts .5 like half a year -6 months-).

EndDate: 12/01/2013

But if

Period: 1.4 (counts .4 like 4 months).

EndDate: 10/01/2013. eos' way 10/25/2013.

[LaRetta]

Or

Let ( d = start date ;

Date ( Month ( d ) + 12 * period ; Day ( d ) ; Year ( d ) )

)

Sent from ipad

EDITED. You want .5 to mean 5 months? That is not mathematics of any kind

[LaRetta]

ok

Let ( [

d = startdate ;

y = Int ( Period ) ;

m = Mod ( Period ; 1 ) * 10

] ;

Date ( Month ( d ) + m ; Day ( d ) ; Year ( d ) + y ) )

)

...not tested//

[Enigma20xx]

Thanks LaRetta.

But still has the same mistake.

If period is a whole year (1,2,5... or 1.5 one and a half years, 5.5 five years and a half), works fine because .5 is counted as 6 months but if the period is 1.4 months or 3.9 three years and nine months. enddate is wrong.

I have made a novice (as my profile says I am lol) partial solution:

Let (

[ MonthPeriod = 10 * ( Period - Int ( Period ) ) ] ;

Date ( Month ( StarDate ) + MonthPeriod ; Day ( StarDate ) ; Year ( StarDate ) + Int ( Period ) )

)

But only works if MonthPeriod (Months) are less or equal to 9, I miss 10, 11 and 12 months.

Sorry, I din't see your last post. I'll try that and tell you how it goes. Thanks again.

[comment]

As LaRetta says, you need to decide: if "1.5" means "one and a half years" (i.e. one year and six months), then "3.9" must mean "three years and nine tenths of a year" (i.e. three years and 10.8 months). OTOH, if "3.9" means "three years and nine months" then "1.5" must mean "one year and five months".

Knowing what exactly is being calculated could be helpful.

[LaRetta]

My last calc assumes the decimal is number of months but it is confusing and I would suspect your users will misinterpret as well. Suggest whole number of months instead ... Validating it also.

[Enigma20xx]

I apologize to all, I didn't see LaRetta's edited post.

At first 1.5 as a year and a half was fine.

And then I thought what would I do if the period would be a year and nine months, but no worries, complete years and years and halfs are enough.

I have another issue.

By now, we can get next enddate up. How could this be related to the current date to see the current next end date?

I.e.

startdate: 10/01/2010

Period: 1.5.

enddate: 04/01/2012 but that date is already gone, today the enddate should be 10/01/2013.

[comment]

See if this helps:

http://fmforums.com/...te/#entry387325

[Enigma20xx]

Thanks for your advice LaRetta.

My users is myself , I don't have the knowlege to build a commercial solution for anybody. I just implement little step by little step some of my databases.

Is not easy to learn while building but what I'm able to do now compared to two years ago looks like if I were a proffesional, .

Thanks comment.

That post solved the problem. I will use only months. 1.5=18, 5=60.

You all are great.

[Enigma20xx]

One thing solved, another issue appears. How silly I am.

I.e.

I have the starting date of a contract and payments’ periodicity.

I want to create every month those, lets say, invoices that must be created that month in particular.

Electricity provision: every 2 months.

Water provision: monthly.

Now, with your advice, I can set start date, period and then see the next enddate, depending on current date. Works great.

**comment's code**

Let ( [

today = Get ( CurrentDate ) ;



elapsedMonths = 12 * ( Year ( today ) - Year ( StartDate ) ) + Month ( today ) - Month ( StartDate )

- ( Day ( today ) < Day ( StartDate ) ) ;

elapsedIntervals = Div ( elapsedMonths ; N )

] ;

Date ( Month ( StartDate ) + N * ( elapsedIntervals + 1 ) ; Day ( StartDate ) ; Year ( StartDate ) )

)

I wanted to know next enddate to pick THIS month’s ones (trough a script) and generate the invoices but I didn’t notice that:

I can never get this month’s enddate. It always shows NEXT enddate but not the ones for this month if there’s any. Now I see July’s, but in July I’ll see August’s, so I could never run the script to create THIS MONTH'S enddates.

[comment]

I wanted to know next enddate to pick THIS month’s ones (trough a script) and generate the invoices but I didn’t notice that:

I can never get this month’s enddate. It always shows NEXT enddate but not the ones for this month if there’s any. Now I see July’s, but in July I’ll see August’s, so I could never run the script to create THIS MONTH'S enddates.

I am afraid you have lost me at this point...

[Enigma20xx]

Sorry about that comment.

I’ll try to explain myself with an example.

StartDate: 01/01/2012 (same for both customers)

Customer 1. Period: monthly. (So enddates: 02/01, 03/01, 04/01, 05/01, 06/01, 07/01... all of 2012).

Customer 2. Period: every two months. (So enddates: 03/01, 05/01, 07/01, 09/01... all of 2012).

In a giving month, lets say, March and June, I’d like to run a script to generate that month’s invoices.

In March 2012 will be customer 1 and customer 2. (2 new records).

But in June 2012 will be only customer 1. (1 new record).

Now I see next enddates (for example July for both customers) but in July I won’t be able to find records with and enddate July (to run the scritp), because there’s non at that month been in that month. The enddate then, shows next one up, which is August.

As it works now works correctly so I can see next enddates. No problem at all.

But I also need, when an enddate exists, that information to “stay” there the whole month so I can make a search and generate the records.

I'm thinking of another field that shows current month enddates.

I think with or without the example I have made a mess again .

[Enigma20xx]

I think I have solved it.

Another field (calculation, result date (do not store calculation results--recalculate when needed)).

Let ( [

today = Get ( CurrentDate ) ;

elapsedMonthsB = 12 * ( Year ( today ) - Year ( StartDate ) ) + Month ( today ) - Month ( StartDate )

- ( Day ( today ) < Day ( StartDate ) ) ;

elapsedIntervalsB = Div ( elapsedMonthsB ; N )

] ;

Date ( Month ( StartDate ) + N * ( elapsedIntervalsB 

+ 1

 ) ; Day ( StartDate ) ; Year ( StartDate ) )

)