Running # of days

[panelman]

Suppose I am running an ongoing test that involves measuring weights at some interval of days. Columns are arranged as:
Column A = date of initial weight
Column B = # of days between inital weight and weight1
Column C = weight1
Column D = # of days between initial weight and weight2
Column E = weight2
ect., etc.

As the weights are entered through the test process I would like for the number of days from start to be calculated automatically for each weight without adding any more columns or manually inputing the date each weight is taken.

 

[al_b_cnu]

Hi, Welcome to the board!

A worksheet change event should do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 And Target.Column <> 5 Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Text = "" Then Exit Sub

Dim vInitDate As Variant

vInitDate = Cells(Target.Row, 1).Value
If IsDate(vInitDate) Then
    Application.EnableEvents = False
    Cells(Target.Row, Target.Column - 1).Value = Int(Date - vInitDate)
    Application.EnableEvents = True
End If

End Sub

to install, right-click the sheet tab, select 'View code' & paste above code into code window.