Count Months in Date Range

[Elvis72]

I have a Demobilization and an Mobilization for every Personnel record related to a project.

I need to accomplish this:

Name Mobilization Demobilization Total Months

Jack Sprat 1/1/2008 1/1/2009 12
June Carter 1/1/2008 2/1/2009 1

All I can locate to complete this is counting the records or days in a date range.

Any help is GREATLY appreciated!~

 

[nobull613]

You could try a DateDiff function, assuming the Mobilization and Demobilization fields are on the same row for each personnel. Something like
DateDiff("m",[Mobilization],[Demobilization])

This should count the number of months from Mobilization to Demobilization.

 

[missinglinq]

For this purpose you'll probably want to calculate days instead of months:

DateDiff("d",[Mobilization],[Demobilization])

The problem with using month is that one day or 28/29/30/31 days, depending on the particular month, will both be counted as a month. With 1/1/2008 to 4/30/2008, for example,

DateDiff("m",#1/1/2008#,#4/30/2008#) will return 3 months

but

DateDiff("d",#1/1/2008#,#4/30/2008#) will return 120 days, or what most people would consider to be 4 months!


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Elvis72]

Thank You!~ Thank You!~ Thank You!~

 

[Elvis72]

I am actually calculating man months so if someone starts on 2/1/2009 and ends on 4/1/2009 I do need it to show 2 months instead of three.

 

[kjv1611]

Well, are you just dividing by 30? Perhaps you could divide by 30, and then include something like: if it's less than 15 days, it's not a month, or if it is 15 days or greater it is a month? Depends upon what your company's requirements are, I would think.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

What about this ?
Round((Demobilization-Mobilization)/30)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

I am actually calculating man months so if someone starts on 2/1/2009 and ends on 4/1/2009 I do need it to show 2 months instead of three.

Either month or day used in the interval arguement will get you 2 months in this example! But if the example should be

2/1/2009 and 4/30/2009

using months will still get you 2 months, when in fact it's 88 days, which is quite a bit more than 2 months. Unless, of course, my son-in-law, the Navy Master Chief, is right when he says "You really have to be in the Armed Services to understand the true meaning of 'close enough for government purposes'!"


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!