Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Needed | Excel Discussion (Misc queries) | |||
Formula Needed | Excel Discussion (Misc queries) | |||
Formula help needed | Excel Worksheet Functions | |||
Little more help needed for my IF formula | Excel Discussion (Misc queries) | |||
Formula Needed... | Excel Worksheet Functions |