subtract exactly one month from a date?

[picnichouse]

Any way to reliably subtract one month from a date. whether it's march 29th, january 3rd, or anything else?

Thanks,

Andrew

[mr_vodka]

What do you mean exactly by subtracting one month.

Does that mean 30/31 days? or do you mean the same day but prior month? (Feb 28 will result Jan 28) ?

[Lee Smith]

Hi Andrew,

mr_vodka is correct, it does depend on what you are really looking for. FileMaker does have a strong date function.

For instance, with a field called Date, your calculation could be as simple as:

c_Date        Calculation, Date Result   =

Date - 30

Lee

[Fenton]

Also be aware that FileMaker adjust for invalid dates.

Try the following, for an entry Date_ of: 12/31/2005

Date ( Month (Date_) - 1; Day (Date_); Year (Date_) )

It returns 12/1/2005

What else could it do? So it seems you'd have to trap for those end days which exceeded the previous month's end, if you truly want the "previous month."

[picnichouse]

do you mean the same day but prior month? (Feb 28 will result Jan 28) ?

This one.

[LaRetta]

As Fenton says, it won't work without writing in exceptions. What do you want returned when the month ends in 31 days and the prior month only has 30? What do you want when the month is March 30 and the prior month is February which ends at 28? Should exceptions move back one day or forward?

[LaRetta]

This will jump one month back but stay on matching day. Exceptions: When the prior month's matching DAY does not exist. If DAY doesn't exist, this calc will produce LAST day of the prior month (and accounts for leap year/cross year. Calc conforms to FM's internal dates and responds properly). I tested it quite a bit. Result is Date:

Let ( [

dayWant = Day ( dateField ) ;

dayEnd = Day ( dateField - dayWant ) ] ;

Date ( Month ( dateField ) - 1 ; Day ( Min ( dayEnd ; dayWant ) ) ; Year ( dateField ) ) )

To jump forward: [color:green]Incorrect adjustment removed.

LaRetta

[LaRetta]

If Business Rules dictate moving 'the exceptions' forward one day instead then the calc needs adjusting (red), thus:

Let ( [

dayWant = Day ( dateField ) ;

dayEnd = Day ( dateField - dayWant )[color:red] ;

one = dayWant > dayEnd ] ;

Date ( Month ( dateField ) - 1 ; Day ( Min ( dayEnd ; dayWant ) ) [color:red]+ one ; Year ( dateField ) ) )

I removed the prior tweak as it broke. And I changed the red parameter from fwd to one (as it seemed to better clarify its boolean purpose). I KNOW this calc can be sweeter - I feel it - it's on the tip of my brain but not coming out. This stuff drives me!!

L

[picnichouse]

Thanks LaRetta, this is great!

[idstechnical]

Having looked at this calc I like it!!

How do I accomodate 29th February as I keep getting the following error:

'The value of this field must be a valid date in the range of years 1 to 4000 and should look like 25/12/2003'

Help!!

Cheers

James

Edited November 14, 2005 by Guest

[idstechnical]

Never mind I am being an idiot.

If i actually put a leap year in it works fine this year is not a leap year is it!

Cheers anyway

James