How to obtain the transit time

[Paul]

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

 

[Auric__]

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.)

 

[Paul]

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

 

[Auric__]

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.)

 

[Paul]

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

 

[Auric__]

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.)

 

[Paul]

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