How can I increase the value in a cell based on the changing date

[Guest]

At work, we need a "cycle" number that increases by 7 every day. What kind of
formula could a use that would the value in "A1" (Say 5) and increase by 7
every day?

Thank you.

 

[Guest]

a1 = 5
A2 = A1+7
drag formula from A2 down

 

[Bob Phillips]

Private Sub Workbook_Open()
With Worksheets("Sheet1").Range("A1")
If .Offset(0, 1).Value < Date Then
.Value = .Value + 7
.Offset(0, 1) = Date
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


This will put the date in B1 as well.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you Bob,

Your reply seems quite suitable but After I post the code into "view code"
section, save the *.xls and return to the spreadsheet, the speadsheet is
still blank.

 

[Peo Sjoblom]

You have to close and open Excel

 

[Rick Rothstein \(MVP - VB\)]

At work, we need a "cycle" number that increases by 7 every day. What kind

of
formula could a use that would the value in "A1" (Say 5) and increase by 7
every day?

Put your value in A1 as you have proposed. Also put today's date in another
cell (for this example, say B1). Then put this formula in (for this example,
say A2)..

A2: =A1+7*(TODAY()-B1)

For each day that passes, A2 should display the 7 more than the previous
day. I am confused by your highlighting the word "cycle" in your original
posting though... is there some kind of maximum number for which the values
displayed in A2 should "start over"? If so, use this formula instead...

A2: =A1+MOD(7*(TODAY()-B1),X)

where you should replace X with the maximum value before recycling the
count. By the way, the value of X should be a multiple of 7.

Rick