Calculate length of years, months and days in Access 2003

Tags:
Microsoft Access 2003

Dear sir,

I own my business in different cities. My employs keep on transferring from one city to the other.

I want to calculate length "years, months and days" of employs in a city and then calculate total service an employ in all the cities. Answer should be in "years, months and days".

Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

To calculate length of years, months and days in Access 2003, you need
to create a function. The function will use a begin date and an end date
to return a value in the units you have specified. Use the Age()
function to calculate an age in years for a begin date as the person’s
date of birth and the end date of your choice. The current date is the
most commonly used end date, although you may desire to use a date of
death as the end date or another date that makes sense to your task.
Input the “StartDate” and use varAge = DateDiff(“yyyy”, varBirthDate,
Now) if you want the current date as the end date.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • AshishSingh10
    Hi,
    The very first constraints that are required are two dates: -
    1. Date Obtained
    2. Date of Expiry

    The above dates are available in the first two columns.

    Column 3: - Years: DateDiff("yyyy",[DateObtained],[DateOfExpiry])

    Column 4: -
    Months: DateDiff("m",[DateObtained],[DateOfExpiry])

    Column 5: -
    Days:
    DateDiff("d",DateAdd("m",[Months],[DateObtained]),[DateOfExpiry])



    The code can be implemented as: -

    SELECT TestDate.DateObtained, TestDate.DateExpired,
    DateDiff("yyyy",[DateObtained],[DateExpired]) AS Years,
    IIf(Day([DateObtained])<=Day([DateExpired]),DateDiff("m",[DateObtained],[DateExpired])-[Years]*12,DateDiff("m",[DateObtained],[DateExpired])-[Years]*12-1)
    AS Months,
    DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateExpired])
    AS Days
    FROM TestDate;

    1,330 pointsBadges:
    report
  • SangPencuriImpian
    =DateDiff("yyyy",[DOJ],Date())+Int(Format(Date(),"mmdd")<Format([DOJ],"mmdd")) & " Years, " & (DateDiff("m",[DOJ],Date()))-((DateDiff("yyyy",[DOJ],Date())+Int(Format(Date(),"mmdd")<Format([DOJ],"mmdd")))*12) & " Months"
    10 pointsBadges:
    report
  • siheang
    thank you your formula is perfect, may you add day because your only year and month don't have day, thanks

    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: