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 Replies
ADVERTISEMENT
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
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 3 Replies
View Related
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
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
May 6, 2014
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:
Expr1: DateDiff("yyyy",[TBL_EmployeeDetails]![D_O_B],Date())
and in months but I would like to know how to return the difference in years and months.
View 1 Replies
View Related
Jun 8, 2015
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?
View 4 Replies
View Related
Jul 3, 2015
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.
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Jan 25, 2006
Friends,
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.
Thanks for any help.
View 14 Replies
View Related
Jul 11, 2006
Hello,
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)
Any advice on this?
Thanks, Paul
View 5 Replies
View Related
Oct 18, 2007
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.
Any help with this would be greatly appreciated
View 3 Replies
View Related
Sep 17, 2013
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.
View 2 Replies
View Related
Jun 7, 2005
Good morning all,
I currently have the following expression in a query, to go back 6 months from the current date.
Between DateAdd("m",-6,Date()) And Date()
Does anyone know of a way from the current date, go back to the end of the previous month ( 30th, or 31st) then go back 6 months?
hope that made sense.
Thanks
Kevin
View 2 Replies
View Related
Dec 12, 2005
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
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
View 1 Replies
View Related
Mar 19, 2007
Hey
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
View 14 Replies
View Related
Jan 18, 2006
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.
View 7 Replies
View Related
