I have an assigned SHIP DATE. I need to calculate an expected DUE date which is 9 working days prior to the ship date, and a START DATE which is 16 working days prior to the SHIP DATE. Working days are MON thru FRI. I am not concerned with eliminating holidays at this point but it would be nice, if not too complicated to do that at some point. I see many responses on how to calculate working days between two dates, but nothing on how to calculate a date based on x number of working days prior to a date.
11 6557 zmbd 5,501
Expert Mod 4TB
I really do not see any easy method here.
The logic one would need:
M - F, 5 days
So one would need to determine if more than 5 days between the days then determine if there are non-weekdays and account for those...
So take today 2015-06-23
Go back 9 days... so if we take 5 into 9 we get 1.8, or 7 days or one week... we need to pull off the workdays out of the week, so 5 * 1 week... gives us 5 days.... 9 less the 5 leaves us 4 days to step thru...
So take 2015-06-23, subtract the 7 days to give us 2015-06-16... now loop backwards a day (15th) Monday (4-1 = 3), loop back, Sunday... we can skip that day and the next so sub 2 gives us the next Friday the 12th (3-1 =2) loop the next two days out leaves us with a start day of 2015-06-10
So now that we have the logic worked out (and that took me awhile to figure) we can write our function
Caution this is air code... meaning I have not checked this in the VBA-Editor. I'm following the logic above and building as I go... The function would be called in the Query or via VBA etc as..
>>One Note: I don't usually provide the code upfront; however, I can see where this would have a great deal of value for many people... and I might be able to use it too. FindDueDate([Date_value_or_field],days_to_back_calc)
... FindDueDate(#06/23/2015#,9) - Public Function FindDueDate(zDateIn as Date, zBackDays as Integer)
-
Dim zdueDate as Date
-
Dim zWeekCount as Long
-
Dim zDaysLeft as Long
-
Dim zCounter as Integer
-
'
-
on error goto z_error
-
'
-
zDueDate = zdatein
-
zCounter = zBackDays
-
'
-
'so find out how many 7 day weeks there are
-
zWeekCount = fix(zBackDays/5)
-
'
-
'and then subtract those days from the datein
-
If zWeekCount>0 then
-
zDueDate = zDueDate - (zWeekCount*7)
-
endif
-
'
-
'and we need to find out how many days were accounted for
-
zDaysLeft = ZCounter - (zWeekCount *5)
-
zcounter = 0
-
'
-
'now loop thru the remaining days to see if there are any weekends
-
for zcounter = 1 to zDaysLeft
-
'
-
'if the day is a Sunday then skip it and Saturday
-
if weekday(zDueDate) = 1 then
-
zDueDate = zDueDate -2
-
else
-
'otherwise just back up a day...
-
zDueDate = zDueDate -1
-
end if
-
next zcounter
-
'
-
'now return the date calc'd
-
FindDueDate = zDueDate
-
z_resume:
-
Exit Function
-
'
-
z_error:
-
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "FncFindDueDate"
-
Resume z_resume
-
end Function
Once again... it's very late here and I have not checked the above for syntax/logic errors - it's midnight my time and I'm running on fumes.
I already see where this could be built on to handle forward calcs too,...., I leave that to someone else for tonight.
That's a cool function Zmbd. I've been using this one provided Microsoft: https://support.microsoft.com/en-us/kb/115489 - '**********************************************************
-
'Declarations section of the module
-
'**********************************************************
-
Option Explicit
-
-
'==========================================================
-
' The DateAddW() function provides a workday substitute
-
' for DateAdd("w", number, date). This function performs
-
' error checking and ignores fractional Interval values.
-
'==========================================================
-
Function DateAddW (ByVal TheDate, ByVal Interval)
-
-
Dim Weeks As Long, OddDays As Long, Temp As String
-
-
If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
-
VarType(Interval) > 5 Then
-
DateAddW = TheDate
-
ElseIf Interval = 0 Then
-
DateAddW = TheDate
-
ElseIf Interval > 0 Then
-
Interval = Int(Interval)
-
-
' Make sure TheDate is a workday (round down).
-
Temp = Format(TheDate, "ddd")
-
If Temp = "Sun" Then
-
TheDate = TheDate - 2
-
ElseIf Temp = "Sat" Then
-
TheDate = TheDate - 1
-
End If
-
-
' Calculate Weeks and OddDays.
-
Weeks = Int(Interval / 5)
-
OddDays = Interval - (Weeks * 5)
-
TheDate = TheDate + (Weeks * 7)
-
-
' Take OddDays weekend into account.
-
If (DatePart("w", TheDate) + OddDays) > 6 Then
-
TheDate = TheDate + OddDays + 2
-
Else
-
TheDate = TheDate + OddDays
-
End If
-
-
DateAddW = TheDate
-
Else ' Interval is < 0
-
Interval = Int(-Interval) ' Make positive & subtract later.
-
-
' Make sure TheDate is a workday (round up).
-
Temp = Format(TheDate, "ddd")
-
If Temp = "Sun" Then
-
TheDate = TheDate + 1
-
ElseIf Temp = "Sat" Then
-
TheDate = TheDate + 2
-
End If
-
-
' Calculate Weeks and OddDays.
-
Weeks = Int(Interval / 5)
-
OddDays = Interval - (Weeks * 5)
-
TheDate = TheDate - (Weeks * 7)
-
-
' Take OddDays weekend into account.
-
If (DatePart("w", TheDate) - OddDays) < 2 Then
-
TheDate = TheDate - OddDays - 2
-
Else
-
TheDate = TheDate - OddDays
-
End If
-
-
DateAddW = TheDate
-
End If
-
-
End Function
I reposted their code for grins.
zmbd 5,501
Expert Mod 4TB
Figures that the wheel would already be invented that's what I get for checking things before bed!
( rotfl )
zmbd 5,501
Expert Mod 4TB
This ended up in my Inbox for some reason:
mhschof wrote:
zmbd has responded to your question on Bytes.com: Calculate a date using x number of 'working days' from a date.
Reminder: please mark select "choose as best answer" for the reply that solves your question.
*****************
This is an automated message, do not reply
Found this and it worked perfectly. I have users inputting a "Request Date". Upon entering a date, I need
Access to populate a second field ("Due Date"). When they enter their
Request Date, I want Access to set the default value of Due Date =
Request Date + 9 Business Days (Holidays don't matter. Just want
Saturday and Sunday taken out). Any thoughts?
Assuming [Request Date] is on a business day, add two weeks, i.e., 10
business days. Then subtract one day unless that day is a Monday where
three days are subtracted. Note that adding 14 days does not change
the Weekday value.
DateAdd('d', IIf(Weekday([Request Date]) <> 2, -1, -3), DateAdd('d',
14, [Request Date]))
If [Request Date] can fall on a weekend, Saturdays subtract two days
and Sundays subtract three days.
DateAdd('d', IIf(Weekday([Request Date]) < 3, -3, IIf(Weekday([Request
Date]) = 7, -2, -1)), DateAdd('d', 14, [Request Date]))
zmbd 5,501
Expert Mod 4TB
@mhschof:
1+ Those functions may work; however, I think that you will be stifled with their approach. There's also a bit if checking you have to do to make sure you are using the correct set of calculations whereas either MS' function shown by JForbes or My function simply account for the weekend regardless of the date.
2+ Your original question asked for a back calculation, now it seems that you have changed your mind. MS function will already account for this and you can always hardcode the lead days. My function can be modified to calculate in either direction.
You need to decide what you want.
3+ Using the function that either MS or I have supplied along with the concept of using a table containing the holidays will allow you to implement the holiday checking. You will have a more difficult time doing this with the date functions you've proposed.
NeoPa 32,556
Expert Mod 16PB
I offer this in case it can be helpful : - 'MoveWD moves datThis on by the intInc weekdays.
-
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
-
MoveWD = datThis
-
For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
-
MoveWD = MoveWD + Sgn(intInc)
-
Do While (Weekday(MoveWD) Mod 7) < 2
-
MoveWD = MoveWD + Sgn(intInc)
-
Loop
-
Next intInc
-
End Function
It's not optimised but is reliable ;-)
zmbd 5,501
Expert Mod 4TB
I had forgotten about the sgn() and of course the mod() is an easier method. I'll have to go in and make some changes to my code!
Very interesting that there are so many of us with little calcs to do this, and there's one in the Excel via standard addin, yet not one for Access...
NeoPa 32,556
Expert Mod 16PB DateAdd() actually has an Interval parameter that can take the value 'w' to indicate move along x number of weekdays.
Embedded in the Help page description are the following two snippets : DateAdd():
To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").
DateAdd(): Note
When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.
The latter is from Access 2010. This was not highlighted previously.
The DateAdd() function with "w" - Weekday interval doesn't always work, atleast for me it didn't. That's what I tried a while back and instead ended up implementing the DateAddW() function provided by DateAdd() "w" Interval Does Not Work as Expected. If I remember right, it worked for me in Access 2013 and not Access 2007. I didn't try Access 2010.
NeoPa 32,556
Expert Mod 16PB
I point you back at my earlier post Jeff :-D
Read it carefully all through.
Then have a laugh ;-)
Oh, HaHa, whoever wrote that should be a politician.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: MyOracle |
last post by:
Hi everybody,
I just curious about change date(0000-00-00) to date (00-00-0000) in
mysql.Can anyone tell me about that.
Thanks.
izmanhaidi.
|
by: Rustan |
last post by:
Hi
Im using GregorianCalendar to find out the current years week numbers.
When the user chooses a week number in a dropdown i want to show that week
in a table with the corresponding dates.
For...
|
by: Scott Kilbourn |
last post by:
Hi,
Does anyone know how to accurately calculate the number of days that have
elapsed since 01/01/0000? I'd appreciate any help anyone could give me.
Thanks
|
by: james |
last post by:
I have a problem that at first glance seems not that hard to figure out. But, so far, the answer has escaped me. I have an old
database file that has the date(s) stored in it as number of days.
An...
|
by: Jason Huang |
last post by:
Hi,
In our C# Windows Form application, we are using the SQL Server 2000 as the
database server.
The Database table MyTable has a field RegistrationDate which represents the
Date a client comes...
|
by: user |
last post by:
Hi,
Let's say I have 2 dates in the b/m format:
Date 1 and date 2
How do I check whether Date2 is later than Date 1?
Date1. 21-Nov-2006 09:00:00 PM
|
by: clintonb |
last post by:
I'm looking for a way to calculate the number of days between two
dates using standard C++ functions.
Would it be as simple as just using the difftime() function and then
dividing that result by...
|
by: Beemer Biker |
last post by:
I cant seem to get that date into any DateTime to make my calculation
directly by subtracting "01-01-0000" from "now".
After reading this:...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |