Calculating Difference between dates

[ananga]

In access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.

 

[ColinEssex]

Check out DateDiff() function.

Col

 

[MStef]

Hello ananga!
Look at "DemoYMDA2000.mdb"

 

[Bat17]

Mstef
Try the dates 31 Dec 2004 to 1 Jan 2005

Peter

 

[EMP]

Take a look at this thread.
http://www.access-programmers.co.uk/forums/showthread.php?t=67472

^

 

[MStef]

Thanks BAT17,
here is a new one.

 

[EMP]

MStef,

31 May 2003 to 1 April 2005 returned Year 2 Month -1 Day -29

^

 

[MStef]

Thanks EMP!
Yes you are right. It happens when it was not tested
enough, (I was in a hurry). I think it's OK now.

 

[jamjarr]

Thank you for supplying the sample database

I am a newbie with Access but an oldie in age.

The sample code works the way that I want but how can I write the values into separate fields called say VMonth,VDay.

I have two fields called StartDate and EndDate and I want to calculate the difference in months and years.

Thanks in antipication.

 

[MStef]

Hello jamjarr!

I don't understand what you want.
This demo work a diferent in years, months and days.

 

[jamjarr]

Sorry for the misunderstanding

The database works fine for me.

However I want to extend the functionality by having the values in the years months and days text boxes in the form. I was wanting to use these values for other uses so i was wanting to write these values to designated fields in the table. Say I have fields in the table called vYears, Vmonths and Vdays and the values in your text boxes are written to those fields when I press a command button called "Save Record".

Any ideas.

Thanks.

 

[DonKHotay]

In access in one field I have one date and in another field I have another date. I want to calculate the number of years.,months and days elapsed between these two date. e.g. suppose in one field I have 30.06.2005 and in other 20.04.2004 then it should show 1 year 2 months and 10 days.

Hi there,
You can easily break apart the date field with the functions Month(), Day(), Year(). So to get the breakdown of elapsed years, months, days add these three fields to your query: Year(DateField1) - Year(DateField2), Month(DateField1) - Month(DateField2), Day(DateField1) - Day(DateField2).

 

[EMP]

Hi there,
You can easily break apart the date field with the functions Month(), Day(), Year(). So to get the breakdown of elapsed years, months, days add these three fields to your query: Year(DateField1) - Year(DateField2), Month(DateField1) - Month(DateField2), Day(DateField1) - Day(DateField2).

DonKHotay,

The expressions may return negative numbers. For instance

DateField2	DateField1	Years	Months	Days
12/31/2004	1/1/2005	1	-11	-30

^

 

[MStef]

Hello Jamjarr!

It's possible, look at "DemoYMD2A2000.mdb",
Try to add a new record, but it's not necessary,
because you can calculate this always yuo need it.

 

[jamjarr]

Calculating Dates

Thank you very much.

It works wonderfully.

Now to the next hurdle.

Regards

 

[DonKHotay]

DonKHotay,

The expressions may return negative numbers. For instance

DateField2	DateField1	Years	Months	Days
12/31/2004	1/1/2005	1	-11	-30

^

true....I guess to do it that way you would need to get the absolute value so Abs(Year()), Abs(Month()), and ABS(Day())

 

[EMP]

true....I guess to do it that way you would need to get the absolute value so Abs(Year()), Abs(Month()), and ABS(Day())

Abs() can remove the negative signs from the numbers. But it can't return the correct numbers.

^

 

[DonKHotay]

hehe, you are right of course....I did not think this through entirely. I guess it's been tackled already but had I attempted this in a more thoughtful manner I might've gone about using lots of Iif's and nested Iif statements. First there would be a test for Leap year, because if there is a leap year involved you would be dividing the difference of the two date fields by 366 instead of 365, and then taking the floor of that to get years elapsed.

... I forsee many problems with that approach, so I would have ended up asking for help from the pros here!