Formula help needed
 

I am working on a template for a work. The document has a column for "# of
Days on Market". I have been looking in help and in my Excel books for a
formula that will add one day to the total number each day the item remains
on the market. I am not sure how to word what I am looking for in HELP. What
I need is ex: Today the item has been on the market 14 days. When I open the
worksheet tomorrow I need it to say 15 days. I looked for something similar
to =Today() but can't seem to find anything. Thanks.

Formula help needed
 

Private Sub Workbook_Open()
Dim RunDate As Date
Const RUN_DATE As String = "__RunDate"

On Error Resume Next
RunDate = Evaluate(ThisWorkbook.Names(RUN_DATE).RefersTo)
On Error GoTo 0
If RunDate < Date Then
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
End If
ThisWorkbook.Names.Add Name:=RUN_DATE, RefersTo:=Date, Visible:=False

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


--
---
HTH

Bob

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



"accessnovice" wrote in message
...
I am working on a template for a work. The document has a column for "# of
Days on Market". I have been looking in help and in my Excel books for a
formula that will add one day to the total number each day the item
remains
on the market. I am not sure how to word what I am looking for in HELP.
What
I need is ex: Today the item has been on the market 14 days. When I open
the
worksheet tomorrow I need it to say 15 days. I looked for something
similar
to =Today() but can't seem to find anything. Thanks.

Formula help needed
 

Thanks - this vb code thing is above my level of experience with Excel. Am I
to change the "Sheet 1" to the name of my tab and the Range "A1" to the
column my data is in?

"Bob Phillips" wrote:

Private Sub Workbook_Open()
Dim RunDate As Date
Const RUN_DATE As String = "__RunDate"

On Error Resume Next
RunDate = Evaluate(ThisWorkbook.Names(RUN_DATE).RefersTo)
On Error GoTo 0
If RunDate < Date Then
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
End If
ThisWorkbook.Names.Add Name:=RUN_DATE, RefersTo:=Date, Visible:=False

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


--
---
HTH

Bob

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



"accessnovice" wrote in message
...
I am working on a template for a work. The document has a column for "# of
Days on Market". I have been looking in help and in my Excel books for a
formula that will add one day to the total number each day the item
remains
on the market. I am not sure how to word what I am looking for in HELP.
What
I need is ex: Today the item has been on the market 14 days. When I open
the
worksheet tomorrow I need it to say 15 days. I looked for something
similar
to =Today() but can't seem to find anything. Thanks.

Formula help needed
 

I am working on a template for a work. The document has a column for "# of
Days on Market". I have been looking in help and in my Excel books for a
formula that will add one day to the total number each day the item
remains
on the market. I am not sure how to word what I am looking for in HELP.
What
I need is ex: Today the item has been on the market 14 days. When I open
the
worksheet tomorrow I need it to say 15 days. I looked for something
similar
to =Today() but can't seem to find anything. Thanks.

Do you have the listing date in a column somewhere? If so, just use either

=TODAY()-A1

or...

=TODAY()-A1+1

depending on if you count the listing day as day one or not. Substitute the
cell number containing the listing date for the A1 that I used.

Rick

Formula help needed
 

There isn't a date in the column that I need to change daily. It is a housing
listing and it states the days the home has been on the market. Example - if
I list a home today I will place a 1 in the "# of days on Market" column.
Tomorrow when I open the workbook I will need a 2 in that column and so on
until the home sells. Thanks for your help.

"Rick Rothstein (MVP - VB)" wrote:

I am working on a template for a work. The document has a column for "# of
Days on Market". I have been looking in help and in my Excel books for a
formula that will add one day to the total number each day the item
remains
on the market. I am not sure how to word what I am looking for in HELP.
What
I need is ex: Today the item has been on the market 14 days. When I open
the
worksheet tomorrow I need it to say 15 days. I looked for something
similar
to =Today() but can't seem to find anything. Thanks.

Do you have the listing date in a column somewhere? If so, just use either

=TODAY()-A1

or...

=TODAY()-A1+1

depending on if you count the listing day as day one or not. Substitute the
cell number containing the listing date for the A1 that I used.

Rick

Formula help needed
 

If the OP can modify the worksheet, a simpler approach is available. Given
"# Days on Market" is in column A. Add "MarketDate" in column B as the date
added to market. In Column A2, add the following code and copy it down:
=Today()-B2

"Bob Phillips" wrote:

Private Sub Workbook_Open()
Dim RunDate As Date
Const RUN_DATE As String = "__RunDate"

On Error Resume Next
RunDate = Evaluate(ThisWorkbook.Names(RUN_DATE).RefersTo)
On Error GoTo 0
If RunDate < Date Then
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
End If
ThisWorkbook.Names.Add Name:=RUN_DATE, RefersTo:=Date, Visible:=False

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


--
---
HTH

Bob

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



"accessnovice" wrote in message
...
I am working on a template for a work. The document has a column for "# of
Days on Market". I have been looking in help and in my Excel books for a
formula that will add one day to the total number each day the item
remains
on the market. I am not sure how to word what I am looking for in HELP.
What
I need is ex: Today the item has been on the market 14 days. When I open
the
worksheet tomorrow I need it to say 15 days. I looked for something
similar
to =Today() but can't seem to find anything. Thanks.

Formula help needed
 

Do you have the listing date in a column somewhere? If so, just use
either

=TODAY()-A1

or...

=TODAY()-A1+1

depending on if you count the listing day as day one or not. Substitute
the
cell number containing the listing date for the A1 that I used.

There isn't a date in the column that I need to change daily.

I wasn't talking about a date that changes daily.


It is a housing listing and it states the days the home has been on the
market.

And my question to you was... Do you have a column for the date the house
was listed (or the date it went on the market) in your spreadsheet? If so,
that cell address for that date is what you should use in place of where I
wrote A1.


Rick

Formula help needed
 

No I don't have a date that the house went to market but I will add that
column and use your suggestion. Thanks for you help!

"Rick Rothstein (MVP - VB)" wrote:

Do you have the listing date in a column somewhere? If so, just use
either

=TODAY()-A1

or...

=TODAY()-A1+1

depending on if you count the listing day as day one or not. Substitute
the
cell number containing the listing date for the A1 that I used.

There isn't a date in the column that I need to change daily.

I wasn't talking about a date that changes daily.


It is a housing listing and it states the days the home has been on the
market.

And my question to you was... Do you have a column for the date the house
was listed (or the date it went on the market) in your spreadsheet? If so,
that cell address for that date is what you should use in place of where I
wrote A1.


Rick

Formula help needed
 

You'll also need a cell to record the date when the property is sold or
otherwise disposed of or your "on market" calculation will just continue to
grow.

Assume:

column A = list of properties
column B = date listed
column C = date sold or otherwise removed from the market

=IF(COUNT(B2),IF(COUNT(C2),C2,TODAY())-B2+1,"")

Once a date is entered in C2 the formula will stop increasing.

--
Biff
Microsoft Excel MVP


"accessnovice" wrote in message
...
No I don't have a date that the house went to market but I will add that
column and use your suggestion. Thanks for you help!

"Rick Rothstein (MVP - VB)" wrote:

Do you have the listing date in a column somewhere? If so, just use
either

=TODAY()-A1

or...

=TODAY()-A1+1

depending on if you count the listing day as day one or not.
Substitute
the
cell number containing the listing date for the A1 that I used.

There isn't a date in the column that I need to change daily.

I wasn't talking about a date that changes daily.


It is a housing listing and it states the days the home has been on the
market.

And my question to you was... Do you have a column for the date the house
was listed (or the date it went on the market) in your spreadsheet? If
so,
that cell address for that date is what you should use in place of where
I
wrote A1.


Rick