This Unstored calculation will show elapsed days: Get ( CurrentDate ) - DateListed
Where date listed is the date field where you recorded the date the property was placed on the market.
THANK YOU! This was driving me crazy!
That works great, but also how would I get not just through today's date, but the last date listed on the market? For example, 12/17/2009 through 3/31/2010 - how would I get just those dates, not just through today's date?
Are both fields defined in the same record? Is this "last date" field empty if it's still listed on the market?
If so, you can do something like this:
If ( IsEmpty (LastDate) ; Get ( CurrentDate ) - DateListed ; LastDate - DateListed )
Where LastDate is the last date the property was listed on the market.
Given that the expression still uses Get ( CurrentDate ) you'll still need to set this up as an unstored calculation.
Instead of just
Get ( CurrentDate ) - DateListed
Perhaps along the lines of :
isempty( LastDateListed ) ; Get ( CurrentDate ) - DateListed ;
LastDateListed - DateListed
This will show how many days the property has been on the market to date (if its not sold)
or if its no longer on the market (last date listed) will show how many days it WAS on for.
I think the problem is the way it was imported, which I didn't have control of. Date Listed is all in one column for each property ID #, so when it first went on the market 12/2/09, that calculates to 201 days. Since it is still on the market, as of 5/14/10, the calculation returns it as 38 days on the market. I put the results in a new column Days On Market next to the Date Listed column.
I don't follow that. How can you tell from the data whether a given property is still on the market or not?
I was supplied with a huge list of properties by month that were/are still on the market. From 9 PDF files by month, of all things. They then highlighted if it sold in yellow. I assume this was all originally done in Word format. Crazy. I had to convert from PDF to Excel then to Filemaker.
Ok, but in your database, how are you indicating that a property is no longer listed? Do you have a field that says "Sold"? A date sold field? or what? You have to have field that records the sale or de-listing date or there's no way to set up a calculation for days on the market that can give you the correct figure for properties that have been sold or de-listed.
Yes, I have a separate column set up that will state "Sold" and the date.
SWS and I posted very similar solutions that will work then.
Thank you for your help.