More Date calc questions

[Edward Newman]

I have a database that tracks service contracts. There is a start date, end date and duration in months. I want to change end date to a calc field and use start date plus duration to end up with the end date. If I add x to start date it is in days. As months have a different number of days, I can't just add monthsx30. Any thoughts outside of a messy date to text to date thing???

[Lee Smith]

Hi Ed,

Actually FileMaker does a good job of figuring out dates. Try this calculation, it should do what you want.

Start Date + x

as long as x are days, this will work.

[Wendy T]

hi Edward,

to add a number of months (I'll use 3 as an example) to a given date (I'll call the field YourDate), use this:

Date( Month(YourDate)+3, Day(YourDate), Year(YourDate) )

As Lee said, FileMaker handles date calcs very well. If you use a calc to add 3 months to an October date, FileMaker will not only give you January but automatically increment the year by 1 as well. Also, if a resulting date would be invalid (eg Feb 30th), FileMaker will return the next valid date (March 1st).

cheers,

Wendy

[DonH]

Let me jump in here because I'm having a problem with this too.

I want to do a find on all records with a date of earlier than 1 year ago. Of course if I enter it manually it is easy. But how do I get basically TODAY - 1 YEAR into a field for a FIND?

I tried doing the calculation first like Wendy said and then pasting it into the field, but instead of a MM/DD/YYYY format I ended up with the number of days since 0001. So that method doesn't work.

What else can I do?

[LiveOak]

A few questions:

What is the calculation you are using?

What is the field type into which you are pasting the result?

How do you define "1 year ago"? 365 days ago? Same date in previous year? What is 1 year before Feb 29th?

-bd

[Vaughan]

Look at the Date() function, it's what you need. The syntax is Date (month, day, year). What you want to do is something like Date (month, day, year - 1) to get last year.

Don't use the Today function, use Status(CurrentDate). Search the forums for the reason.

To cut to the chase, the final function would be...

Date (Month(Status(CurrentDate)), Day(Status(CurrentDate)), Year(Status(CurrentDate)) - 1)

Deconstruct the function to see the basic Date (month, day, year) syntax. That's how Date() works, and you can do anything with it.