Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
Hallo,
Column A I insert a date (Departing date) Column B I insert another date (Arrival date). I would like with Vba (vba because sometimes I might need to adjust the B30 date) once I input a date from cell A30 to obtain in B30 another date plus a value in A20 (transit time) with the following checks: - date in B30 = date in A30 - date in B30 = today's date - date in B30 + A20 (skip Sat. and Sundays) For example: Column A20 = 3 (transit time) Column A30 = 17 jan 2013 (departing time) Column B30 = 22 jan 2013 (arrival time) Thanks for any help and/or suggestions. Regards Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
Paul wrote:
Column A I insert a date (Departing date) Column B I insert another date (Arrival date). I would like with Vba (vba because sometimes I might need to adjust the B30 date) once I input a date from cell A30 to obtain in B30 another date plus a value in A20 (transit time) with the following checks: - date in B30 = date in A30 - date in B30 = today's date - date in B30 + A20 (skip Sat. and Sundays) For example: Column A20 = 3 (transit time) Column A30 = 17 jan 2013 (departing time) Column B30 = 22 jan 2013 (arrival time) I'm not too clear *why* this needs to be VBA. This could be done with a simple formula: =INT((B30-A30)-(INT((B30-A30)/7)*2)) (The inner INT is what handles the weekends.) If it simply *must* be VBA, try this: Sub transitTime() x = Range("B30").Value - Range("A30").Value Range("A20").Value = x - ((x \ 7) * 2) End Sub (Here, "((x \ 7) * 2)" handles the weekends.) -- The number of arguments is unimportant unless some of them are correct. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
Hi,
thanks for the answer. The reason of Vba and not a function is due to the fact that sometimes I need to modify by hand the transit time. If the goods are late from the supplier with 2 drivers I reduce the transit time. So if there a function/formula in the cell, and I need to change the result, it will cancel for the next use of the worksheet One more question if I am allowed. Is possibile to run automatically the sub when I insert the date from A30 down to A60? Thans and regards Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
Paul wrote:
thanks for the answer. Don't thank me. I got the code backward, if you can believe that. I thought you needed the difference calculated between A & B, with the result inserted in A20. Upon rereading your original post, I see that A20 is set by you and you just want to add that value to A and put the result of *that* into B, right? Goddammit. I'm not sure *how* I got it backwards. (Also, what I posted before doesn't accurately take weekends into account.) The reason of Vba and not a function is due to the fact that sometimes I need to modify by hand the transit time. If the goods are late from the supplier with 2 drivers I reduce the transit time. So if there a function/formula in the cell, and I need to change the result, it will cancel for the next use of the worksheet Gotcha. The formula I posted wouldn't work anyway. One more question if I am allowed. Is possibile to run automatically the sub when I insert the date from A30 down to A60? Here's the code to do *what you actually need*, assuming I'm not getting something else back-asswards. It will run any time *anything* is changed, but will quickly exit if what was changed is outside the range A30:A60. Just to clarify, this adds A20 to A30:A60 and puts the results in B30:B60. Put this in the sheet's object in the VBA editor: Private working As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If working Then Exit Sub Dim chk As Range, x As Date, deliveryTime As Long Set chk = Intersect(Target, Range("A30:A60")) If Not (chk Is Nothing) Then deliveryTime = Range("A20").Value working = True For L0 = 30 To 60 If IsDate(Cells(L0, 1).Value) Then x = Cells(L0, 1).Value + deliveryTime If 1 = Weekday(Cells(L0, 1).Value) Then x = x - 1 If (x = Date) Then If (deliveryTime 4) Or _ ((Weekday(x) <= Weekday(Cells(L0, 1).Value)) And _ (deliveryTime 0)) Then x = x + (((deliveryTime \ 7) + 1) * 2) End If Select Case Weekday(x) Case 1, 7 Cells(L0, 2).Value = x + 2 Case Else Cells(L0, 2).Value = x End Select End If End If Next working = False End If Set chk = Nothing End Sub This *seems* to work, for me at least, in a few minutes of testing. It even gives accurate dates if something is marked as having been shipped on the weekend. (I'm assuming that if someone sends something out on a Saturday or Sunday, it doesn't actually start moving until Monday, which is considered day zero in such cases. If that's a bad assumption, the code will need editing.) -- I disapprove of what you say, but I will defend to the death your right to say it. -- Evelyn Beatrice Hall, summarizing Voltaire's philosophy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
I have to thank you anyway because you are trying to solve a question
that will help me a lot in the everyday life using your free time. Before to post the question I tried several solutions, but my knowledge with Excel and mainly Vba are very very limited. The routine is working well. However if I modify the arrival date, lets say in B32 and then insert another date in B33, the value in B32 is back to original value. If I not asking to much is there a way to avoid to update the B32 if I am not working on A32 cell? Considering 3 as value in A20, 18-gen-2013 - 10:00 23-gen-2013 - 10:00 -- OK 19-gen-2013 - 0:00 24-gen-2013 - 0:00 -- OK 20-gen-2013 - 0:00 22-gen-2013 - 0:00 -- ?? In this case I'll use a conditional formatting to highlight the Sundays. Thanks for your time and patiente. Congratulations for your high level of knowledge of Excel/Vba For how long did you learn to achieve this level? Regards Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
Paul wrote:
I have to thank you anyway because you are trying to solve a question that will help me a lot in the everyday life using your free time. Before to post the question I tried several solutions, but my knowledge with Excel and mainly Vba are very very limited. The routine is working well. However if I modify the arrival date, lets say in B32 and then insert another date in B33, the value in B32 is back to original value. If I not asking to much is there a way to avoid to update the B32 if I am not working on A32 cell? Considering 3 as value in A20, 18-gen-2013 - 10:00 23-gen-2013 - 10:00 -- OK 19-gen-2013 - 0:00 24-gen-2013 - 0:00 -- OK 20-gen-2013 - 0:00 22-gen-2013 - 0:00 -- ?? In this case I'll use a conditional formatting to highlight the Sundays. Thanks for your time and patiente. Congratulations for your high level of knowledge of Excel/Vba My code takes the date in column A of the current row (30-60), adds the number in A20, performs magic to figure out weekends, and then dumps the result in column B of the current row. If you change the value of what's in B(30-60), the next time something in A (30-60) is changed, B will get recalculated, regardless of what you've manually entered. To get around that, you could, say, bold the B cell containing your manual entry, then you could change this line: If IsDate(Cells(L0, 1).Value) Then to this: If IsDate(Cells(L0, 1).Value) And _ Not (Cells(L0, 2).Font.Bold) Then There are other possibilities, of course, but you'd need *some* way of telling the VBA that it needs to skip that line. For how long did you learn to achieve this level? Well... I started programming BASIC in 1983 (age 7). I started VB in '98, and VBA in '99 or so... but if you hang around here, you'll see that I'm more of a long-time hobbiest rather than any sort of "expert", and some folks here make me feel like a novice. There are others that *used* to be here. (If you ever get an answer by Tom Ogilvy, I'd take his advice over *anybody else's*. He's a reg at the Excel section of AllExperts now.) -- Your clothes, give them to me, now. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to obtain the transit time
Hallo,
with your additional tip is Ok. If IsDate(Cells(L0, 1).Value) And _ Not (Cells(L0, 2).Font.Bold) Then A 1000 thanks. Regards Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
28/10/2009 10:00 am calculate transit hours 75 hrs = total calcula | Excel Discussion (Misc queries) | |||
How to I subtract two dates & times to obtain a time | Excel Discussion (Misc queries) | |||
How To Obtain The Top 10% | Excel Discussion (Misc queries) | |||
Creating and transit time excel formula | Excel Discussion (Misc queries) | |||
Formula Needed for Transit Time | Excel Discussion (Misc queries) |