• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Calculate future dates based on summing partial values plus dates

Hello!
I have two calculation challenges.
1. I've been searching and testing codes to calculate a promise date. The equation is PO received date + soonest lead time expressed in weeks = promise date.
txtLT = Lead Time and is formatted like this: 12 - 14 WKS
txtPORec'd = Date (ex: 01/02/2022)
txtPromDate = Date + the first two digits of the Lead Time (ex: 01/02/2022 + 12 weeks = 03/27/2022)

2. I also need to calculate an expiration date. The equation is proposal date + 60 expressed in days = expiration date

I found this example of the DataAdd function, but what I need isn't this straightforward. The following does not reference specific textbox values
Code:
dFutureDate=DateAdd("d",60,"mm/dd/yyyy")
I would appreciate your help.
 

BobBridges

Active Member
Let's look at one at a time. For the first problem, you have (if I understand you correctly) two inputs: "<m> - <n> WKS" and #some date#. You want to output "#some date# + <m> weeks = <mm/dd/yyyy>". Is that right? If that txtLT value is always of exactly that format, it doesn't sound hard—but such inputs are never, in reality, reliably of the correct format, so the trick is to spot the inputs that don't match the required format. Can you show us a list of all the variations in that format that you've actually seen in real life, so far?

What I mean, in case it isn't obvious, is that users are inevitably going to type in things like "5 - 7 days", "5-7 weeks", "5- 7 Wks", "five to seven weeks", "5 to 7 WKs", "5 wks to 7 wks" and so on. ("It's impossible to write a foolproof program because fools are so ingenious.") You want your program to correctly interpret as many of these variations as possible, and to correctly spot anything else and produce an error message instead of pretending to understand it and then producing an invalid result.
 
I appreciate your insight and agree 100%. I'm sorry it's taken so long to reply. For some reason, I didn't get an email saying you responded.
I thought the format of 12 - 14 WKS would help when it came to doing the calculation I need. I figured I need to extract from the left and multiply by that. I am pretty new at this, so perhaps there's a better way to calculate PO Received Date (txtPORecDate) x Lead Time (txtLT) = Promised Date (txtPromDate).
 

BobBridges

Active Member
Oh, the calculation part is easy. If you have a date and a number of weeks, the formula in VBA is simply DateRcvd + nWeeks * 7. For example:
Code:
DateReceived = DateSerial(2022, 10, 10)
  ' DateReceived is now the integer 44844, ie, that many days since Jan 1, 1900.
nWeeks=12
DateResult = DateSerial + nWeeks * 7
  ' 44 844 + (12 * 7) = 44 928, or Jan 2 of next year.
Was that your question? Or are you asking how to extract that first number from "12 - 14 weeks"? Or is it how to get a date value out of your dialogue variable txtPORecDate? I'm guessing that the latter may be in any text format, for example "Oct 10, 2022" or "10 Oct 2022" or "2022-10-10" or "Monday Oct 10th, 2022" (and so on). For the latter there's a VBA function that's pretty good at interpreting dates in many text variations.
 
Yes, I have a date and number of weeks, but the date is never the same as well as the number of weeks (Lead Time). Plus, I'm not given one digit, I'm given a range, i.e., 5-7 or 12-14. It makes it harder to extract just the '5' or the '12' to include in the calculation.
DateReceived is 'set' at today's date, but the dates PO's are received are not set, they are different for every order.
nWeeks is also 'set' at 12, but my lead times also are fluid and change with each order.
I'm pretty new, so I apologize if that's causing a challenge.
 
Last edited:

BobBridges

Active Member
Maybe I should back up; I'll bet I've been making assumptions again :).

In your original post you mentioned variable names such as "txtLT" and "txtPromDate". I supposed from this that you were displaying a dialogue window to the user, who wrote values into the window's text boxes; when the user hit <Enter>, the window returned those values to your program in the named variables. I took it, then, that you had some text values and you wanted to calculate one or more dates from them.

You're saying now that the input figures are different for every order, and I'm thinking "Naturally: Your program would have to calculate the values you need every time the user calls up the window, fills in the values and hits <Enter>, and your program would probably also put the results into a worksheet, a new row for each one." But maybe what you mean is that you already have each order entered on a different row, and you want your program to go down the rows and fill in the resulting dates for all the rows?

Let's leave aside, for now, the issue of the text that says "12 - 14 weeks"; that won't be hard to deal with. In fact, none of this sounds challenging to me (because I've been programming for decades; believe me, you get a little more practice and it'll seem easy to you too). What's challenging, apparently, is for me to understand exactly what it is you have. So let's start back at the beginning: You have a bunch of values that represent a date an order was received, and the "lead time" for each PO, and for each PO you need to calculate a date (a delivery date, I suppose, but it doesn't matter). Where are those values now? Are they in a worksheet? And if they're in a worksheet, why do you have variable names for them? Have you already started writing a program?
 
Hi Bob,
Perhaps if I show you what my set up looks like, it will help. I have a worksheet (Master) and a command button titled Data Entry Form that pops up the userform (Master Form). I enter Shop Orders using the Master Form. When I click Add New a row is added to the Master worksheet.
The names I gave the textboxes are:
txtPromDate = Promised Date
txtLT = Lead Time
txtPropDate = Proposal Date
txtExpDate = Expiration Date
The user goes through the form and completes the each of the pages in the multipage control. Here, I'm just showing the Proposal and PO pages.

When the user enters the 1. PO Received Date on the PO page, then goes to the Proposal page and enters the Lead Time, I want the Promised Date to autofill with the results of PO Received Date + Lead Time Weeks = Promised Date. For every order the dates and lead time will vary.

Then, when the user enters the Proposal Date, I want the Expiration Date to autofill with the results from Proposal Date + 60 days = Expiration Date. For now, 60 days never changes and is the same for every order.

I hope this makes sense. I really, really appreciate your patience and willingness to help me.

81267
 

BobBridges

Active Member
Aha! Ok, that does clarify a bit what you're trying to do. But it isn't clear to me yet which part you're having trouble with. Let's lay out the tasks:

1) User enters PO-received date and lead time.
2) User hits <Enter> (or maybe another button); your program must see this and begin running.
Your program must then:
3) Retrieve the text the user entered (date and lead time)
4) Interpret the lead time, on the assumption that it's in the general form "<m> - <n> weeks".
5) Take the smaller of <m> and <n>, multiply by 7, then add that to PO-received date. This is the "promised date".
6) Plug the promised date into another text box of the user form.
7) Plug some of these figures into a new row on the Master worksheet.

Is that right? Is it complete?

None of this is difficult, but if you've never done it before I get that you're not sure how to approach it. (Actually the way to approach it is to lay out the specific requirements as above; that usually clarifies matters wonderfully.) But which parts of it are you uncertain about? I gather you already have steps 1 through 3 working right; what about the rest?
 
Hi Bob,

1) User enters PO-received date and lead time. Yes
2) User hits <Enter> (or maybe another button); your program must see this and begin running. Yes, the button they would hit is either "Add New" or "Update and Save" to execute the calculation and populate the Promised Date.
Your program must then:
3) Retrieve the text the user entered (date and lead time)
4) Interpret the lead time, on the assumption that it's in the general form "<m> - <n> weeks". This is the part of the code that stumps me. How can make sure the calculation is performed on <m>. Plus, <m> needs to be able to be a single or a double digit.
5) Take the smaller of <m> and <n>, multiply by 7, then add that to PO-received date. This is the "promised date". Yes!
6) Plug the promised date into another text box of the user form. Yes, the Promised Date textbox would populate the results of the calculation.
7) Plug some of these figures into a new row on the Master worksheet. Yes, each order occupies one row in the Master worksheet.

Is that right? Is it complete? Yes, I believe you understand what I need. I don't know how to write the code.

But which parts of it are you uncertain about? I gather you already have steps 1 through 3 working right; what about the rest? I do not have steps 1 through 3 working at all.

Thank you for hanging in there with me.


81274
 

BobBridges

Active Member
Ok, you say that step 4 is the part you're asking about. There are (as always) more than one way to do this. Let's start by assuming that the user will always enter it in the form "<m> - <n> WKS" (which is certainly not true, but just to make a start): If that were guaranteed, I think I'd just look for the first space and take whatever comes before it, like this:
Code:
' User puts "12 - 14 WKS" in txtLT.
ps = InStr(txtLT, " ") 'ps is now 3; that's the position of the first space
vw = Left(txtLT, ps-1) 'takes the first 2 chars of txtLT, so vw now is "12"
vw = Val(vw) 'converts the characters "12" to the value 12
' Now you can add vw * 7 to your PO-received date to get the promised date.
The above doesn't help you when the user enters "12-14 WKS", "37 - 55 DAYS" and so on. But try this out and convince yourself it works, and then we can start looking at other possibilities. (In the end you may be forced to try out regular expressions, which can be complex but give you a lot of power and flexibility.)
 
Code:
'Add New Button'
Private Sub AddNew_Click()
' User puts "12 - 14 WKS" in txtLT.
Dim ps, vw As String
ps = InStr(txtLT, " ") 'ps is now 3; that's the position of the first space
vw = Left(txtLT, ps - 1) 'takes the first 2 chars of txtLT, so vw now is "12"
vw = Val(vw) 'converts the characters "12" to the value 12
' Now you can add vw * 7 to your PO-received date to get the promised date.
vw*7=txtPromDate
'syntax error

I get a syntax error.
 

BobBridges

Active Member
Not surprising that I mistyped something, but come on, you gotta give me more information than that. First, if you didn't have me around, could you figure out exactly what the error is by yourself? If so, do it.

If not, then tell me the exact wording of the error message and what statement caused it. (Do you know how to step through your program one statement at a time? It's well worth doing when debugging a program.)
 

BobBridges

Active Member
Oh, I just noticed that you added a line of your own. But you're right, that line makes no sense. Surely you wanted to reverse it, no? That is, not "vw*7=txtPromDate" but "txtPromDate = vw * 7"?

You understand what happens during this statement, right? When you say "x=y", it instructs the program to take the value that is in y and put it in x. Here, your statement says to take the value that is in txtPromDate and put it in vw*7—which, as you say, makes no sense.
 
I correct the line to say: txtPromDate = vw * 7. I tested with today's date as the PO Received Date, so 10/12/2022 * 7 and I got 84, which is the number of days. I need to format the result as a date.

So, I changed the multiplier to 1 and added Format(CDate. I get the same result: 01/11/1900. The correct result is 01/04/2022.

Code:
Private Sub AddNew_Click()
' User puts "12 - 14 WKS" in txtLT.
Dim ps As String
Dim vw As String
ps = InStr(txtLT, " ") 'ps is now 3; that's the position of the first space
vw = Left(txtLT, ps - 1) 'takes the first 2 chars of txtLT, so vw now is "12"
vw = Val(vw) 'converts the characters "12" to the value 12
' Now you can add vw * 7 to your PO-received date to get the promised date.
txtPromDate = Format(CDate(vw * 1), "mm/dd/yyyy")
If I go back to '7' as the multiplier, I get 03/24/1900. If the year was 2023, it's adding 23 weeks, 2 days to the promised date.
Clearly, Excel is not seeing the result as a date. I believe I need to convert the string to a number, and then convert that number to the date.
I'm so close.
 

BobBridges

Active Member
Well, look at the calculations one statement at a time. Vw is the number of weeks for delivery. Vw * 7 is the number of days until delivery. But to get the delivery date, you need to add vw * 7 to the PO-received date, right?

Excel stores its dates as the number of days since 1900-01-01. So if you convert 84 to a date, you'll get the 84th day of 1900, which is 1900-03-24 (which is what you got). What you have to do is start with the PO-received date, add the 84 to that, and that's your promised date.

(Programs never do what you meant, only what you actually said :).)
 
Got it!

Code:
' User puts "12 - 14 WKS" in txtLT.
Dim ps As String
Dim vw As String
ps = InStr(txtLT, " ") 'ps is now 3; that's the position of the first space
vw = Left(txtLT, ps - 1) 'takes the first 2 chars of txtLT, so vw now is "12"
vw = Val(vw) 'converts the characters "12" to the value 12
' Now you can add vw * 7 to your PO-received date to get the promised date.
If IsDate(DateValue(txtPORecDate.Value)) Then
txtPromDate.Value = DateValue(txtPORecDate.Value) + (vw * 7)
End If
I research and researched and finally found what I need to finish the code. I leave for Europe tomorrow and wanted to get this done to make it easier for the user. Mission accomplished. Thank you Bob for your help!
 
Top