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
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.
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).
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
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!!!!
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.
I am wanting to get an expression that will return the difference in years and months between 2 dates. Specifically, I want to know peoples ages in years and months based on a person's D.O.B and todays date. I have managed to do it in years:
How can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?
I am using Access 2013.I am trying to create a query that will count the days difference between two dates. The dates are in the same field. I want to group by Region.So:
tblRegion = RegionID tblStatus = StatusDate
I know how to use the DateDiff when it is two different fields, but I can't figure out how to do it from the same field.
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
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?
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?
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.
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.
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.
I have a form with four fields, date1, date2, date3 and date 4. These have all a mm/dd/yyyy format. I need to add an unbound control which will count in months and years the difference between the dates.
Ex. date1: 01/01/1970 date2: 05/05/1980
date3: 06/06/1990 date4: 06/30/2001
Fields date3 and date4 may be blank, therefore the code should consider only counting the first two.
I am building a Db that will report against individual transactions. Each transaction will be on a specific day. Now I will need to slice and dice the data in a number of different ways depending on what the end user wants to see. Therefore I need to build a date table that will aggregate all the individual days by Week, Month, calendar year, fiscal year...etc. Being a rookie, I was simply going to build a table that has all the days for the last three years and then the corresponding weeks, months etc in columns to the right. Because of this design, I will have over 1,000 records. Could I do it differently to keep the size of the table down? For example use ranges as a cell value (Our week 1 is named "Feb 1st Wk", it is 1/30/06-2/5/06 for fiscal Yr 2006, 1/31/04-2/6/05 for fiscal Yr 2005...etc)
The table I want to run the query on has a date in the format of dd/mm/yyyy. I want to be able to run a query so that the user can type in a month and all records with dates in that month will be shown.
I have done this before many years ago but now have a mental block and cant figure it out.
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17. Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String On Error GoTo Err_Handler
If IsNull(StartDate) Then getTimeElapsed = "No startdate" Exit Function End If
Dim YY As Integer Dim MM As Integer Dim DD As Long
' use current day if EndDate is not supplied, ' + 1 makes both days inclusive. EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate) DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative. If DD < 0 Then MM = MM - 1 DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate) End If
YY = MM 12 ' integer division. MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _ MM & IIf(MM < 2, " month ", " months ") & _ DD & IIf(DD < 2, " day", " days") Exit Function
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
I have a Transactoin Table that holds the following fields:
TransID (*Primary Key Numerical Value) OrderID (Non Unique Number Value, One Order can have multiple Transactions attributed to it. Status (Numerical Value ranging from 0 to 32, each is a different phase in the life of a transaction such as 0 means Transation Created, 1 through 31 are various actions with 32 beign the transaction closing) Date (The date in which a particular phase of a particular transaction happened.) Reason (The Description of a phase. This is the only text field whose contents are relative to the Status field in the same table.)
I need to find the amount of time passed from the first Transaction to the last transaction of each individual order, which will be displayed in a report. Unfortunately not every order goes through all 32 phases, in fact most dont. The individual orders have a random amount of phases that they go through but each have a starting one (which ranges from 0 onwards) and an ending one (once again it is not necessarily 32). The dates correspond to the numbers in an individual order which means that if an order has the following phases:
1 2 4 6 Then the dates of each phase will be one after the other like the Status Number as shown above. Anybody knows how to do this? I need to find how much time passed from the first transaction to the last for each individual order qand display it in a report.
