Hi !
Can you help me calculate the length of a contract in months, only for this year, where for eg, one can be valid from 11/1/2014 to 10/31/2016 and another from 8/31/2016 to 12/31/2018 ?
I'm a beginner
Tnx !
Hi !
Can you help me calculate the length of a contract in months, only for this year, where for eg, one can be valid from 11/1/2014 to 10/31/2016 and another from 8/31/2016 to 12/31/2018 ?
I'm a beginner
Tnx !
Not clear on what you want. Do you want the number of months between 11/1/2014 to 10/31/2016? or just between 1/1/2016 and 10/31/2016?
The general function to get the difference in months is:
DateDiff(interval, date1, date2)
If you mean between #1/1/2016# and #10/31/2016#, you will need to add 1. I manually calculated the number of months to be 10.
TryYou might have to use VBA code to be able to calculate the number of months.Code:DateDiff("m", #1/1/2016#, #10/31/2016#)+1
Hi!Not clear on what you want. Do you want the number of months between 11/1/2014 to 10/31/2016? or just between 1/1/2016 and 10/31/2016?
The general function to get the difference in months is:
DateDiff(interval, date1, date2)
If you mean between #1/1/2016# and #10/31/2016#, you will need to add 1. I manually calculated the number of months to be 10.
TryYou might have to use VBA code to be able to calculate the number of months.Code:DateDiff("m", #1/1/2016#, #10/31/2016#)+1
Thank you for you answer. But i need the number of months, only for 2016, for:
Valid from Valid to len (Years) 9/1/2013 9/1/2016 3 1/1/2015 12/31/2017 2 8/1/2014 12/31/2016 2 1/1/2016 12/31/2017 1 1/1/2016 12/31/2017 1 1/1/2015 3/31/2016 1 4/2/2014 3/27/2016 2 4/2/2014 3/27/2016 2 3/1/2015 2/28/2017 2 6/1/2013 6/1/2016 3 8/1/2013 1/1/2016 3 10/1/2013 10/1/2016 3 10/1/2013 10/1/2016 3 10/1/2016 12/31/2016 0 1/1/2015 12/31/2016 1 1/1/2015 12/31/2016 1 1/1/2014 12/31/2016 2 10/20/2016 12/31/2017 1
Still not sure what you want. Is this in code or in a query?
I'm going to make several assumptions...
The examples you provided are in a table.
The calculations for number of months will be done in a query.
ALL "Valid_from" dates will begin on the first of a month.
ALL "Valid_to" dates will end on the last day of the month.
Right off the bat, you have problems. In the first row of dates, the "Valid_to" date is the first of a month. not the last day of the month.
In the last row of dates, the "Valid_from" is 10/20/2016. So do you count Oct as a whole month?
Create a new query.
Add your table that has "Valid_from" and "Valid_to" fields. Note that there are no spaces in the field names.
In a empty column, paste in the following:
The dates for Jan 1, 2016 and 12/31/2016 are hard coded. I would rather have them reference text boxes on a form, but you can make that change later.Code:LenInMths: DateDiff("m",IIf([Valid_from]<#1/1/2016#,#1/1/2016#,[valid_from]),IIf([Valid_to]>#12/31/2016#,#12/31/2016#,[valid_to]))+1
Any of the Length in months that are RED, I would consider a problem. The dates in RED are the cause.Code:
Valid_from Valid_to lenYears LenInMths 9/1/2013 9/1/2016 3 9 1/1/2015 12/31/2017 2 12 8/1/2014 12/31/2016 2 12 1/1/2016 12/31/2017 1 12 1/1/2016 12/31/2017 1 12 1/1/2015 3/31/2016 1 3 4/2/2014 3/27/2016 2 3 4/2/2014 3/27/2016 2 3 3/1/2015 2/28/2017 2 12 6/1/2013 6/1/2016 3 6 8/1/2013 1/1/2016 3 1 10/1/2013 10/1/2016 3 10 10/1/2013 10/1/2016 3 10 10/1/2016 12/31/2016 0 3 1/1/2015 12/31/2016 1 12 1/1/2015 12/31/2016 1 12 1/1/2014 12/31/2016 2 12 10/20/2016 12/31/2017 1 3