Macro for calculating differnce between two cells

[saminexcel]

Hi,

I am using Excel 2007 and trying to create a simple macro for claculating differnce between two cells.

<TABLE style="WIDTH: 222pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=295><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20 width=89>Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=70>Start Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 width=68>End Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=68>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>7/26/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>14:06</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>14:07</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>0:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>7/26/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>14:07</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>0:00:00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>7/26/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>14:08</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>0:00:00</TD></TR></TBODY></TABLE>

Column A = Date
Column B = Start Time
Column C= End Time
Column D= Total


The code i've put to this is

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 Then
With Target
If .Value <> "" Then
.Offset(0, -3).Value = Format(Now, "mm/dd/yyyy")
End If
End With
End If
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 Then
With Target
If .Value <> "" Then
.Offset(0, -2).Value = Format(Now, "hh:mm:ss")
End If
End With
End If
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 Then
With Target
If .Value <> "" Then
.Offset(-1, -1).Value = Format(Now, "hh:mm:ss")
End If
End With
End If
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 4 Then
With Target
If .Value <> "" Then
.Offset(0, 2).Value = (-1) - (-2)
End If
End With
End If
enditall:
Application.EnableEvents = True
End Sub


I am not getting the differnce in column D as total time taken.

Please advice where i'm getting this wrong.

Thank you,
Sam

 

[Weaver]

If I'm being honest, it's anything but simple.

What are you trying to do here?

.Offset(0, 2).Value = (-1) - (-2)

I'm guessing this is where your problem lies

 

[Ruddles]

Is there a reason why you're doing this via a macro as opposed to using a simple worksheet formula?

If you have a start time in B2 and an end time in C2, then to calculate the elapsed time you just put =C2-B2 in cell D2.

 

[saminexcel]

Yeah, you are correct, even i feel its just getting complexed....

as you are referring to, i've tried to place the differnce of end time - start time in the cell as value.

Please help me to correct this.

Thanks,
Sam

 

[saminexcel]

Well, using fromulas makes the sheet more prone to getting corrupt (as the end user often fiddles with the cells containing formulas.

Also, i intend to use this code to use it through out the column in this sheet.

Please help.

Sam

 

[Weaver]

Unless you're totally fixated on doing this with a macro, try the following formula in column D (starting in row 2)

=if(count(A2:C2)=3,C2-B2,"")

So if there's no data in the first 3 columns, the output will be a null string, and you'll only get a result if all 3 cells are populated.

I think maybe you're not fully conversant with the way the worksheet_change event works. I'd avoid it if I were you.

For example, you're checking for changes in column 4, when it's the change in column 3 (i.e when someone enters an end time) that you need to look for, since you can't do the math without it.

if it really has to be code, try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column < 4 And WorksheetFunction.CountA(Cells(Target.Row, 1).Resize(, 3)) = 3 Then
        Application.ScreenUpdating = False
        Cells(Target.Row, 4).Value = Cells(Target.Row, 3).Value - Cells(Target.Row, 2).Value
        Application.ScreenUpdating = True
    End If
End Sub

 

[Toonies]

Hi check this out

http://www.cpearson.com/excel/datetimeentry.htm

 

[Ruddles]

Well, using fromulas makes the sheet more prone to getting corrupt (as the end user often fiddles with the cells containing formulas.

In that case you have to pay special attention to the way your VBA is written, to ensure that it copes with instances where the user inserts or deletes rows or columns, sorts the data, etc.

If formulae are critical and your users are not to be trusted, protect the worksheet.