time calculations in vba

[Nick]

I need help on this one.
I have an excel sheet where the user can enter a date (in column A)
and starting time (in column B). In column C they can enter how much
time a certain action will take. Column D to AA are hours ( 0 to 23).
The idea is to get something like this:
A B C D E....
16/03/2004 13:45 0:17
So after the data is enter I need to calculate how many minutes of the
time user entered falls in certain hour. For the above example it was
15 minutes in 13 hours en 2 minutes in 14 hours (13:45+00:17 = 14:02).
Now, the thing is that I need those calculations to take place in VBA,
not in excel worksheet and I could use some help...

 

[JE McGimpsey]

One way:

Assuming hours start with hour 0 in column D and end with hour 23 in
column AA:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const dONEHOUR As Double = 0.0416666666666667 '1/24
Dim rCell As Range
Dim dDuration As Double
Dim dFirstHour As Double
Dim dHours(0 To 23) As Double
Dim nStart As Long
Dim i As Long

With Target(1)
If Not Intersect(.Cells, Range("A:C")) Is Nothing Then
With Cells(.Row, 1).Resize(1, 3)
If Application.Count(.Cells) = 3 Then
dDuration = .Item(3)
nStart = Hour(.Item(2))
dFirstHour = Application.Min(dDuration, _
TimeSerial(nStart, 0, 0) + dONEHOUR - .Item(2))
dHours(nStart) = dFirstHour
dDuration = dDuration - dFirstHour
For i = nStart + 1 To nStart + 24
If dDuration > 0 Then
dHours(i Mod 24) = _
Application.Min(dDuration, dONEHOUR)
dDuration = dDuration - dONEHOUR
If dDuration <= 0 Then Exit For
End If
Next i
Application.EnableEvents = False
With .Offset(0, 3).Resize(1, 24)
.NumberFormat = "hh:mm"
.Value = dHours
End With
Application.EnableEvents = True
End If
End With
End If
End With
End Sub

 

[Frank Kabel]

Hi JE
nice one!

 

[dreamer]

I can't get it working. I've pasted this in the sheet1's code window bu
when I enter data nothing happens..

 

[JE McGimpsey]

The code won't put times into the cells until all the cells in columns
A:C for that row are filled with numbers (including dates and times).
Set a breakpoint at the first line to see if the event is being called
(it should fire every time you make an entry).

If it fires, but skips the code after the If Application.Count(...)=3
line, some of your data may have been entered as Text.