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

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.

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

a1 = 5
A2 = A1+7
drag formula from A2 down
--
Traa Dy Liooar

Jock


"Cray_z77" wrote:

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.

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

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)

"Cray_z77" wrote in message
...
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.

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

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.

"Bob Phillips" wrote:

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)

"Cray_z77" wrote in message
...
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.

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

You have to close and open Excel

--
Regards,

Peo Sjoblom

"Cray_z77" wrote in message
...
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.

"Bob Phillips" wrote:

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)

"Cray_z77" wrote in message
...
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.

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

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