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

C

Cray_z77

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

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

Cray_z77

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

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?
Click to expand...

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
 
Top