Modules & VBA :: Calculating X Years / X Months And X Days Between Two Dates

Jul 19, 2013

Find the difference between two dates is simple if you're simply looking for the number of days between them. However, if you're looking to express the difference in a bit more user-friendly way, you can use the following code to calculate the difference in years, months, and days. The code accounts for zero values, plurals and the Oxford comma. Further, it automatically sets the later of the two dates to the End date, so the dates can be used interchangeably.

Examples:
June 4, 2010, July 3, 2012 returns "2 Years and 29 Days"
June 4, 2010, July 5, 2011 returns "1 Year, 1 Month and 1 Day"
June 4, 2010, June 5, 2010 returns "1 Day"

Code:
Function DiffOfTwoDates(dtmDate1 As Date, dtmDate2 As Date) As String
' Written by Will Knapp, Freelance Access Developer, 2013
Dim dtmStart As Date, dtmEnd As Date
Dim strDiff As String ' Resulting String
Dim yDiff As Integer ' Year Difference
Dim mDiff As Integer ' Month Difference
Dim dDiff As Integer ' Day Difference
Dim CommaLoc As Integer

[code]....

View Replies


ADVERTISEMENT

Queries :: Calculating Years / Months And Days

Aug 6, 2014

I'm trying to breakdown a date into years, months, and days. Simple right?

The code I've been using for months returns a negative month if it happens to be the next month. Instead of returning 1 Year, 1 Month, 23 Days, it will instead return 1 Year, -1 Month, 23 Days.

Code is:

Code:
Months: IIf(IsNull([TIR]),0,IIf(Day([TIR])<=Day(Date()),DateDiff("m",[TIR],Date())-[Years]*12,DateDiff("m",[TIR],Date())-[Years]*12-1))

View 4 Replies View Related

Difference Between Two Dates Into Years Months Days

Aug 31, 2006

Hello to everybody.

I have a problem in calculating the difference between two dates and displaying the output into Years Months Weeks Days. My problem is that dividing the number of days by 30 and requesting an integer is not accurate enough. I have looked at Pat and Jons examples shown in these threads

http://www.access-programmers.co.uk/forums/showthread.php?t=67472

http://www.access-programmers.co.uk/forums/showthread.php?t=89051&highlight=calculating+number+months

which either method would suit me as the accurate answer is what I am after.

My problem is that not all of my records would have a start and end date. Therefore both examples would result in an error.

Can anyone please give a hint on how to perform the calculations on those fields which have dates recording the results but ignoring those fileds which do not have a start / end date.

I am having a bad day and I'm sure the answer is so easy that I am going to kick myself but having played around with the modules by limited knowledge is not enough.

Many thanks in advance

Ian

View 3 Replies View Related

Years / Months / Days Question

Jun 5, 2006

I have a form that I'd like to place a date onto, after entering a from date and a to date. What I'd like to see on the form, after entering my two specified dates, is:

"You have xx years, yy months and zz day left on your timeline." where xx is the number of years, yy the number of months andzz the number of days.

If I entered dates of say, 01/01/01 for my from date and 12/31/06 as my to date, how can I accomplish something like what I want?

Thank you so much in advance! I'm looking forward to your suggestions!!!!

View 2 Replies View Related

Calculating Years And Months

Jan 5, 2007

Hi,

i have a displayed(textbox) birthdate (txtDoB) and a textbox "input_date" (txtInputDate) inside form "form Two".

how do i calculate the years and months difference upon clicking the "OK" command button at "form Two"?

the result of the years and months will be displayed at a textbox (txtResult) at a form called "form Result".

thank you.

View 2 Replies View Related

Date Difference In Years, Months, Days

Jan 14, 2007

Hello,

I have two fields in a table, both containing dates. I would like to write a query that returns the difference between the dates in an year, month, day format (i.e. 26y 2m 3d). If that isnt possible, can the difference be returned as years,days (i.e. 26y 63d).

Thankyou.

View 4 Replies View Related

Calculating Age In Years And Months From Date Of Birth

Jan 19, 2006

I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.

Thanks

View 14 Replies View Related

Reports :: Calculating Current Age In Years And Months

May 7, 2013

I am trying to calculate the age on the day the report is printed in Years and months ( and display it thus) in a report.e.g. 6 years 4 months

View 2 Replies View Related

Calculating Number Of Days Using Dates

Mar 15, 2006

I have a library system and i need to work out the number of days from the date the book was due and the current date. Could anyone help me.

View 2 Replies View Related

Problem With Calculating Days Between Dates

Dec 9, 2005

I have a form with two date fields: "Initiation_date", and "Correction_date". There is a third field called "Duration". I would like to store the values of all three fields in the corresponding table. "Duration" (Long Integer) would be the number of days from "Initiation_Date" to "Correction_Date" if Correction date is there, or to Date() if Correction date is Null.

I can use an unbound text box to get "Duration_cal" using the following as a control source:

=IIf(IsNull([VCor_date]),DateDiff("d",[VInit_date],Date()),DateDiff("d",[VInit_date],[VCor_date]))

But I cannot get that value (No.of Days) in to the "Duration" field. The reason I want to have "Duration" stored as a number is that it is being used in a query for another calculation (total days used for selected records).

Is there a simple way to accomplish this?

Thanks

View 1 Replies View Related

Queries :: Calculating Days Between Dates From A Specific Record

Dec 14, 2013

I have a query that records a status update, and the date it was made by an employee etc. The table has a secondary key related to ... lets say a project table.

E.g.
Status Id; EmployeeID;ProjectsID;Status;Date

I want to calculate the average amount of days it takes for a status change occur.

View 1 Replies View Related

Add Months And Years

Oct 29, 2007

Hello mates, :cool:

I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P

View 1 Replies View Related

Modules & VBA :: Calculating Number Of Days Between Records

Sep 10, 2013

I am trying to calculate the number of days between incidents.

If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.

View 14 Replies View Related

Calculate Age In Years And Months

Feb 8, 2005

I need a formula or a function that will return a person's age in years and months. All the functions and formulas that I have seen to date and that I have used only calculate a person's age in years. Can anyone help?

View 2 Replies View Related

Converting An Age Calculation To Years And Months

Jul 22, 2005

i am calculating an age from (Birthdate - date())/365.25 and this works fine except it gives me a value which has a decimal place (ie 6.7 years old). How can i convert it to 6 years and so many months?

View 5 Replies View Related

Modules & VBA :: Working Days Between Two Dates

Jan 26, 2015

I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.

Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.

[code]...

View 8 Replies View Related

Modules & VBA :: Grab And Calculate Days Between Two Dates

Feb 11, 2015

What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).

The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.

The formula will then be:

simpledate - convertedcasenumberdate = days between.

(simpledate will always be the latest date of the two).

View 7 Replies View Related

Modules & VBA :: Calculating With Dates

Sep 11, 2013

i want to calculate a date

Code:
Dim enddate As Date
Dim startdate As Date
Dim running_time As Double
startdate = Me.startdate
running_time = Me.running_time
endate = stardate + running time
Me.enddate = enddate

The running time is in year.If i calcluate right now, the System things the running time is in days. How can i say that the running time is in years.

View 5 Replies View Related

Parse A String With Years Month And Days....

Dec 4, 2006

Solution to parse the strings below and get number of months
1) 10 years 5 months 5 days - Should give 5
2) 6 years 10 months 22 days - Should give 10

View 2 Replies View Related

Calculating Fiscal Years

Dec 4, 2007

Hi,

I have the following function:

Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function

I call on the function in my query to determine the FiscalYear for a date.
Expr1: GetFiscalYear([ProdDate])

It sends back the correct Fiscal Year. However, when I try to put criteria in for Expr1 to show only Fiscal Year 2007 instead of all fiscal years, it gives me a data mismatch type error.

The [ProdDate] has the following formula:
Prod_Date: IIf([40Day]="True",DateAdd("d",-40,[Date Code]),DateAdd("d",-50,[Date Code])) where [40Day] returns True or False to determine if I should subtract 40 or 50 days from the Date Code. The Date Code is in the format ##/##/#### and is sometimes null.

I have tried everything I could think of and am completely stumped. Thanks in advance for any help!!!

View 2 Replies View Related

Modules & VBA :: Getting Age In Years With One Decimal Place (3.5 Years)

Mar 24, 2014

I have a table that lists computers I want to know the age of them for replacement purposes. I am using this function to populate a textbox on a form that updates my table. the results I am getting is a negative whole number I would like to get a positive number with one decimal place. so if computer was shipped September 24, 2010 my textbox would say 3.5 not -3

Here is my function

Code:
Private Sub Form_Load()
Dim theDate As Date
Dim age As Integer
theDate = Nz(Me.compDate.value, 0)
If theDate > 0 Then
age = DateDiff("yyyy", Now(), theDate)
Me.compAge = age
End If
End Sub

View 4 Replies View Related

Calculating Years Employees Have Worked For

Jun 3, 2005

hi, i want to calculate how long each person has worked at a company.

i am using access 97.

would i be able to enter something in the criteria that would work this out for me?

something like year([START DATE]) - year(Now())
this does not work but i want to find out the number of years a person has worked for the company from the field START DATE .

thanks for you help.

View 8 Replies View Related

Queries :: Counting Days Between Dates - Vacation Period And Work Days

Sep 11, 2014

I have a form called subfrm_vactions

rowsource is a query called qryVacations

the query should calculated two things

1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)

2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)

the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want

simply i want to calculate the work days.

View 5 Replies View Related

Queries :: Number Of Days Between Two Dates With Irregular Days Worked

Apr 14, 2014

I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.

I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.

View 1 Replies View Related

Grouping/Calculating Months

Sep 30, 2006

I have a table which lists rentals, all have start/end dates and a rental fee p.a.

I want to query this table to show rentals that are effective for any part of 2 dates, [Enter Start Date] and [Enter End Date]. The purpose of this query is to calculate rental income between these 2 selected dates.

Now I have this much working, but I want to group all the figures by month, in order to produce a graph to view the rental income by months.... this bit has me stuck....:confused:

How can I group all these records, when I only have 2 dates... for eg, start 01-01-06 and end 31-07-06...
I want to see figures for Jan06, Feb06, Mar06...July06etc... rather than just a single total figure for the entire period...

Any ideas?
Help! I'm completely stuck here and have spent hours searching the internet but haven't found anything suitable...

View 10 Replies View Related

Queries :: Building A Query To Pick Between Set Months / Days But Different

May 4, 2014

I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).

As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved