My table : Id Start End
1 2013.11.20 2014.01.10 2 2014.01.17 2014.01.30 3 2014.01.06 2014.01.27 4 2014.02.04 2014.02.23 5 2014.02.07 2014.02.17 6 2014.02.21 2014.03.08
How to calculate total number of unique days? Date ranges are overlapped.
In a database, I have the following date fields that I use to track projects that start and stop.
START1
STOP1
START2
STOP2
START3
STOP3
Is there a way I can use a query to calculate the total days the project was worked on? For example if I enter 1/1/06 in START1, 1/10/06 in STOP1, 2/1/06 in START2 and 2/15/06 in STOP2, 3/1/06 in START3 and 3/30/06 in STOP3, I need to calculate the total days. I need it to work regardless if there are 3 start/stops, 2 or just one start/stop.
I really appreciate the help.
Jim
I have a contract management database where contracts have a start date and an end date. I also have a date parameter set up whereby I can show live contracts between overlapping records.
I want to be able to count the number of days for each contract that is live between the dates of the parameters.
For example my contract might run from 1st to 30th November but I might want to report from 10th October to 10th November so the number of days I need the query to return is 10 days as my contract is not live before the 1st November. Or my query might run from 1st October to 31st December, then I would need it to show 30 days as it covers the whole of this contract.
I am making a basic hospital management system in Access 2013.I have two tables named "Bed" and "Receipt". Bed(BedID,AssignedDate,PatientID,DischargeDate,Bed Charges) Reciept(ReceiptID,PatientID,BedCharges) I want to calculate "BedCharges" by calculating the number of days using "AssignedDate" and "DischargeDate" and then multiplying with a constant amount of charges per day. Also the BedCharges calculated in "Bed" Table also needs to be in the "Receipt" table. How can I count the number of days and then calculate the "BedCharges" in both the tables?
View 8 Replies View RelatedI'm trying to calculate the number of days between two dates using the iif statement
Fields:
[LDW] "Last Day Worked"
[ReturnedDate]
DESCRIPTION: If ReturnedDate is null, then calculate the datediff "d" between LDW with Now(), if not, then calculate the datediff "d" between LDW with ReturnDate.
I tried this but it didn't work at all.
Days Absent: Iif(isnull([ReturnedDate],(DateDiff("d",[LDW],Now())),(DateDiff("d",[LDW],[ReturnedDate])))
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure],
Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
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).
I need to calculate the final date based on the begin date and the number of days.
The name of the fields are: sdatainicio; diasatribuidos; sdatafim.
What's the formula we can use to calculate days between no of days between date job is open and today's date in a query. I am using formulat below formula but the DATE_OPEN and Today (todays date) pops us as enter parameter value. I dont want those enter parameter windows pops us. The date job is open is in a linked table and linked table is getting the information from BPCS software. The date in Liinked is in format YYYYMMDD.
Total Days: DateDiff("d",[Date_Open],[Today])
The SQL Details are below. AUNPRDF_AAA802 is a linked table and information gets updated on 12 am everyday
SELECT DateDiff("d",[Date_Open],[Today]) AS [Total Days], AUNPRDF_AAA802.AAAGE AS Age, AUNPRDF_AAA802.AAORD AS [Job Number], AUNPRDF_AAA802.AAWKSP AS Workshop,
AUNPRDF_AAA802.AASWKT AS WorkType, AUNPRDF_AAA802.AANAME AS [Customer Name], AUNPRDF_AAA802.AANME AS [Service Admin], AUNPRDF_AAA802.AASDE1 AS Complaint,
[Code] ....
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
your help would be much appreciated
merry christmas to you all
Jabez
Dear All,
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
'xxxxxxxxxxxxxxxxxxxxxxxx
Can someone please direct me to the right path.
Your help is highly appreciated.
qwerty70
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.
My subreport provides appointment stats for every month and is grouped by month in the main report. I manage to include the number of days for each month using:
=DateSerial([YearNo],[MonthNo]+1,1)-DateSerial([YearNo],[MonthNo],1)
in a text box, and it works fine.
But I don't know the syntax to get these numbers:
the number of weekend days worked in that month, and the number of weekend day not worked in that month My field is call App_date.
Tried this but does not work - the numbers it returns are incorrect (in every month): =Count(Weekday([App_Date]))
The subreport's is based on a select query with only the Appointment table. It only has four "fields" - that work fine.
They are: the appointment date and its date parts - defined in numbers.
App_date
MonthNo: DatePart("m",[App_Date])
YearNo: DatePart("yyyy",[App_Date])
DayValue: Weekday([App_Date],2)
how to get my weekend work and not work total day count?
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
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.
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.
For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2).Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation.
Code:
Option Compare Database
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
Dim intCount As Long
intCount = 0
[code]...
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
I have a query that has four date variables:
Admit Date
Discharge Date
Report Begin Date
Report End Date
I can calculate the number of days with the DateDiff function when the "Admit Date" and "Dischage Date" are within the range of the report with no problem.
The problem arises when the "Admit Date" is prior to the "Report Begin Date". In this case, the difference needs to be "Report Begin Date" and "Discharge Date".
To make this more complicated, in some cases there is no "Discharge Date", so the calculation needs to look at the first two senerio's and then calculate the days based on the "Report End Date".
Expr2: IIf([Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))
Expr2: IIf([Admission Date]<[Beginning of Report] And [D/C Date / Time]>[End of Report],DateDiff("d",[Beginning of Report],[D/C Date / Time]),DateDiff("d",[Admission Date],[D/C Date / Time]))
Expr3: IIf(IsNull([D/C Date / Time]) And [Admission Date]<[Beginning of Report],DateDiff("d",[Beginning of Report],[End of Report]),DateDiff("d",[Admission Date],[End of Report]))
expr4: IIf([Admission Date]>[Beginning of Report],DateDiff("d",[Admission Date],[D/C Date / Time],0))
Above are failed attempts to get the formula right.
Can anyone assist me in getting this to work correctly.
Thanks
Dan:mad:
I hope you can help. I have a database and I am trying to work out the working days between the start date and the end date of a programme. This will not take into account any weekends or bank holidays. I have created a separate table called tblHolidays which has a list of the Bank Holidays. The majority of it works but out of 1700 records 50 don’t. These are ones in which it starts on 1 month finished on another or has a bank holiday. It works with certain bank holidays but not all of them. Bank holidays go back to the beginning of 2006
The following is the code I have used:
Public Function WorkingDays2(Start_Date As Date, End_Date As Date) As Integer
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
Start_Date = Start_Date + 1
intCount = 0
Do While Start_Date <= End_Date
rst.FindFirst "[HolidayDate] = #" & Start_Date & "#"
If Weekday(Start_Date) <> vbSunday And Weekday(Start_Date) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
Start_Date = Start_Date + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function
I then used the SQL
Select WorkingDays2 ([start_Date], [End_Date]) As CountDays
This is driving me crazy!!:mad:
I have a date range of 1/01/2005 to 12/25/2005 for example. I need a formula to return the number of days between these dates excluding weekends. Basically I need to know how many business days are between these dates. Thanks for the help.
View 1 Replies View RelatedI am woking on a project full of nightmares. My latest, and the one I am currently unable to solve, involves trying to calculate the number of days between Billing Dates.
I have a table named "Date" - I didn't design this!
It contains a field named "Date"
If I query for just that Field I get the following:
Date
9/8/2005
8/8/2005
7/8/2005
6/8/2005
5/9/2005
...
I need to figure out a way to determine the number of days between the bills so I can calculate the average cost per day.
It seems like it should be an easy thing using like the datediff function or something, but since they are in separate records (rows), I can't figure it out. I have tried and tried and tried.
Any suggestions would be VERY much appreciated.
I have a database with 10-12 fields containing dates certain things were completed. There is also a field for the 3 different locations we have and a program year 2006, 2007, or 2008. What they would like to do is click a button and get a list to choose the first date they which to compare, then a list to choose the second date, calculate the number of days between the two dates, select the program year from a list, and a location from a list. Can anyone help me with how to do this if it's possible?
The fields look something like this:
Prog Year Location DateA Date B DateC DateE
2006 G date date date date
2006 G date date date date
2006 P date date date date
2008 D date date date date
2007 P date date date date
For Example, I might want 2006 Program Year, Location P, calculating days between DateA and DateB, except that the next time I might want to choose a different set of criteria.
Any ideas?
THANK YOU!
I have an access database used for recording training events. I was able to calculate the number of days each training event took by using
Datediff("d",[Training Date From],[Training Date To]+1)
I was really chuffed with this until my boss asked what happens when the training only lasts half a day! I added the time to the above fields to make them date/time fields but can't see a way to calculate in half days.
As well as this the other problem is that training could be over a few days. As an example I'd like to be able to input that the training started at 9am on Monday 29th May and ended at 12 noon on Thursday 1st June and get the result of 3.5 days. The full days would all end at 5pm but I wouldn't be recording that, just the total days. I tried changing the datediff function to hours in the hope I could somehow discount the non working hours but that didn't work! I'd ideally like the final result to be in days, ie 0.5, 1 etc. It would always be in whole or half day increments - don't need things to be overly complicated!
Any help will be very gratefully received. I'm definitely not an expert as you can probably tell!! :eek:
I have a database to monitor incoming emails from our contractor & my superior wants it to have control like the following situations:
1. If no respond yet, to calculate today's date minus received date
2. If responded, to calculate respond date minus received date
If i use the following expression, it does not relate to if the emails have no respond yet or have been responded.
IIf(IsNull([Reply Date]),DateDiff("d",[Due Date], Now()))
I'm trying to create a sub to count how many days between 2 dates excluding Holiday and weekend. Using the DateDiff() function is not allowing me to exclude certain days.
View 2 Replies View Related