Number of days between two dates calculation???

[burnerofhells]

How would i calculate the number of days between two dates in filemaker? The dates are in two differnt fields and in the format mm/dd/yyyy. It is generated from a drop down calendar.

Someone selects the start date in one field and the end date in another field. I then need a calculation that will know that there is a certain number of days between the two dates.

I cant figure out how to do this because of the format of the field.

This solution needs to work with file maker 7.

Thanks

Edited March 19, 2008 by Guest

[The Big Bear]

burn

look at the attached file

both date fields are date format with the different field being formatted as a number.

hope this helps

enddate-startdate

datetest.zip

[CobaltSky]

I cant figure out how to do this because of the format of the field...

Hi burnerofhells,

You've told us that your problem is with the field format, but you haven't said if the fields you're using are date fields or text fields - and you haven't told us whether mm/dd/yyyy is standard date format for the region and Operating System formats where you're located. Both these things may have a bearing on the answer.

If StartDate and EndDate are indeed date fields, the answer you've already got from The Big Bear that will (sort of) work - although you might want to include a test to confirm that there are values in both fields - otherwise you'll get some disconcertingly large numbers (eg -733134) returned when one of the date fields is empty. To address this issue, you could use a slightly more complex calculation expression such as:

If(not IsEmpty(StartDate) and not IsEmpty(EndDate); EndDate - StartDate)

If StartDate and EndDate are text fields, you might need to go a little further. In that case, assuming mm/dd/yyyy is the standard date format for your region, you could use:

If(not IsEmpty(StartDate_txt) and not IsEmpty(EndDate_txt);

GetAsDate(EndDate_txt) - GetAsDate(StartDate_txt)

)

...however if the standard format in your region is not mm/dd/yyyy (eg if you are outside the US) you will need to do something a little more tricky to get your database to recognize the non-standard date format. Here's one way to achieve that:

If(not IsEmpty(StartDate_txt) and not IsEmpty(EndDate_txt);

Evaluate("Date(" & Substitute(EndDate_txt; "/"; "; ") & ")") -

Evaluate("Date(" & Substitute(StartDate_txt; "/"; "; ") & ")")

)

One of the three methods above should work, depending on your location and the data format of your input fields. :wink2:

[burnerofhells]

Hey, thanks for all the help! that should help me a lot.

I believe the fields are standard date fields. I used a field with a drop down calendar to get the date. I assume that is a standard date field.

[CobaltSky]

I believe the fields are standard date fields. I used a field with a drop down calendar to get the date. I assume that is a standard date field...

Maybe, maybe not. You can apply a drop-down calendar object controller to most kinds of input fields (including both date fields and text fields).

The way to tell what data type the fields are is to go to the fields tab of the Manage Database dialog:

• File >Manage >Database...

...and see what it says in the Type column adjacent to the fields in question. :wink2:

[cterrell16]

I'm getting a large negative number.  I'm assuming it's because we chose the start date as a text field instead of a date field.  Can I just change it to a date field without loosing any of the data.  The data in the text field are dates at this time only.

[comment]

Can I just change it to a date field without loosing any of the data.

It depends on whether the contents of the field are valid dates in the date format being used by your file.

Try defining a calculation field with the result type set to Date =

GetAsDate ( StartDate )

then do a search for ? in this field. If no records are found, and all the dates look alright, then the answer is yes. Probably. We cannot foresee every possible way your file may be screwed up.

I should add here that if you are subtracting StartDate from EndDate,  and it is your StartDate that is defined as a text field, then you should be seeing a large positive number. So already there is something here that isn't covered by your description above.

Edited August 6, 2015 by comment