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 RepliesGood Afternoon All,
I am in need of you help in calculating this. Basically, I don't want the code to count zero. For example, today is 3/19/2008 - Date() should equal = 1 and not zero..
Here's some more examples:[lastfollowup] being 3/18/08
Date() - [LastFollowup] = 2
Here's my code:
DaysOpen: ((DateDiff("d",[LastFollowup],Date(),2))-(Int(DateDiff("d",[LastFollowup],Date(),2)/7))*2)+IIf(Weekday(Date(),2)>5,5-Weekday(Date(),2),0)+IIf(Weekday([LastFollowup],2)=6,1,0)-IIf(Weekday([LastFollowup],2)>Weekday(Date(),2),2,0)
Thank you,
John
I am trying to calualate the business from an Order Date. The Business Days are Monday thru Friday excluding Holidays. It is for a form that will Highlight (conditional Format) Order over five business days old that have not been completed. Has anyone done this before?
View 2 Replies View RelatedHi,
Is there any access formula that can calculate the net business days between two dates.
Regards,
Jatz
SELECT
[(2) MAX BATCH].ID_CASE,
[(2) MAX BATCH].MaxOfDT_BATCH,
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE
FROM
[(2) MAX BATCH] INNER JOIN NOLDBA_LOG_DISBURSEMENT_DETAIL ON
([(2) MAX BATCH].MaxOfDT_BATCH = NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_BATCH) AND
([(2) MAX BATCH].ID_CASE = NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE);
Can someone tell me how to find the ID_CASE's where
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE is less
than or equal to 2 business days (M-F) of the MaxofDT_BATCH in this query?
I have a query that calculates business days. However if my end date is missing the query has error codes because the date its looking for is null. Can show me were to put the nz function.
here's the module for the business days
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' "Calculating the workdays between Dates"
' Note that this function does account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0
Do While DateCnt <= EndDate
If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays
End Function
and here's the query
SELECT [Reques_ID], Work_days([Date_Received],[To_Director]-1) AS Turnaround_To_Director,
Work_days([To_Director],[From_Director]-1) AS Turnaround_From_Director, Work_days([To_VP],[From_VP]-1) AS Turnaround_From_VP, Work_days([Position_Number_Requested],[Position_Number_Recieved]-1) AS Turnaround_For_Position_Number, Work_days([Date_Received],[Approval_to_mgr]-1) AS Turnaround_EPS, Work_days([Date_Received],[JOIS_Posted_Date]-1) AS Turnaround_To_Posting, Work_days([Approval_to_mgr],[JOIS_Package_Return_Date]-1) AS Turnaround_For_Package_Return
INTO tbl_turnaround
FROM Artifact;
I have seen a few modules that let you count how many business days between two dates. What I am looking for is how to count 10 business days from another field.
Say for example I have a txt box that auto populates =Date(). I want to have another txt box that will count 10 business days ahead and put that date in the box.
This is so I can have the db show a due date for reports sent in.
Anyone seen/done/have code for this?
Hello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
Is there a function in Access that will allow you to add business days to a date? I want to pull a date with a query and then add 5 business days to it to display in a report. Anyone know if this is possible?
Thanks.
Hi, I'm trying to get some date tracking stats...
trying to get the average number of days between two dates using formula:
=Avg(DateDiff("d",[DateRec],[DateNumb]))
where datenumb was the first date app was entered into db and
where daterec is the date the first part of app was recorded.
this works but I'm assuming it's counting straight calendar days. I was wondering is there a way to get it to count only business days and not weekends or holidays in access???
I need to calculate the difference between to datetime fields. However, i need to exclude weekends, holidays and non-business hours. I also need to return the result in hours, minutes, seconds.
can this be done. if so, does anyone know how to do this, or is there code that i can implement and save for future uses.
thx,
john
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
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 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 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 RelatedI am not sure if what I am about to ask is best done in a query, table or report, so I thought I would post this in the query section of the forum.
I have a table that contains the following fields (plus other fields)
RC_START_DATE
RC_DUE_DATE
RC_APPROVAL_DATE
RC_STATUS
On one of my reports, I want to show the "Days Open". If the RC_Status is "Active" I want to calculate the "Days Open" by subtracting RC_START_DATE from Today(). However if the RC_Status is "Closed" I want the "Days Open" to be calculated by RC_APPROVAL_DATE minus RC_START_DATE.
So the question is, what is the best way to do this and how would I write the formula to calculate this?
Thanks for the help.
Jim
I'm trying to step from Excel to Access and I have some issues to translate functions that I used in Excel to Access.This is how it looks in Excel.And I have issues to translate the following function in Excel to Access.
=IF(AND(I6=0),"Done",K6-A$4)
A$4 is related to cell where it states the function =now() Basically what I want is to calculate in the tablecolumn is the following.In the column "Days remaining" I want the remaining days from "date to pay" and today. If the amount states "0" then the outcome must be "done".
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.
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).
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.