Jump to content

subtract exactly one month from a date?


This topic is 6710 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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) ?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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."

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!! :yep:

L

Link to comment
Share on other sites

  • Newbies

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 by Guest
Link to comment
Share on other sites

This topic is 6710 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.