Database formula help

This forum is a self-help system for the exchange of ideas among AppleWorks users.

Database formula help

Postby paul822 » Thu Sep 06, 2007 8:47 am

I would like to use a calculation field in a database using a date from a field and adding 21 years in one field and 22 years in an additional field. I know it's possible. I searched through all my old journals and the forum and couldn't find anything. Thanks in advance.
paul822
 
Posts: 2
Joined: Tue Nov 08, 2005 12:05 pm
Location: Skokie

Re: Database formula help

Postby Barry » Thu Sep 06, 2007 12:27 pm

paul822 wrote:I would like to use a calculation field in a database using a date from a field and adding 21 years in one field and 22 years in an additional field. I know it's possible. I searched through all my old journals and the forum and couldn't find anything. Thanks in advance.


Try this:

TEXTTODATE(DAY('date')&"/"&MONTH('date')&"/"&(YEAR('date')+21))

The Day, Month and Year portions must be set in the same order as the numeric date format used by AppleWorks on your machine. Mine uses dd/mm/yyyy

Change "21" to "22" for the second field.

Double click the fields in Layout to set the Date format to your preferred form.

The formula will give an error if the starting date is February 29. Some thooughts on that later.

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W

Re: Database formula help

Postby paul822 » Thu Sep 06, 2007 2:26 pm

Barry wrote:
paul822 wrote:I would like to use a calculation field in a database using a date from a field and adding 21 years in one field and 22 years in an additional field. I know it's possible. I searched through all my old journals and the forum and couldn't find anything. Thanks in advance.


Try this:

TEXTTODATE(DAY('date')&"/"&MONTH('date')&"/"&(YEAR('date')+21))

The Day, Month and Year portions must be set in the same order as the numeric date format used by AppleWorks on your machine. Mine uses dd/mm/yyyy

Change "21" to "22" for the second field.

Double click the fields in Layout to set the Date format to your preferred form.

The formula will give an error if the starting date is February 29. Some thooughts on that later.

Regards,
Barry


Thanks so much. With some tweaking of my date formats it worked like a charm and saved hours of manual entry of dates.

Paul
paul822
 
Posts: 2
Joined: Tue Nov 08, 2005 12:05 pm
Location: Skokie

Re: Database formula help

Postby Barry » Thu Sep 06, 2007 11:43 pm

paul822 wrote:
Barry wrote:
paul822 wrote:I would like to use a calculation field in a database using a date from a field and adding 21 years in one field and 22 years in an additional field. I know it's possible. I searched through all my old journals and the forum and couldn't find anything. Thanks in advance.


TEXTTODATE(DAY('date')&"/"&MONTH('date')&"/"&(YEAR('date')+21))

The formula will give an error if the starting date is February 29. Some thoughts on that later.


Thanks so much. With some tweaking of my date formats it worked like a charm and saved hours of manual entry of dates.

Paul


You're welcome, Paul.

Here's a revision of the formula that avoids the error when the starting date is Feb 29 (the later dates are shown as Feb 28, the last day of Feb in non-leap years).


TEXTTODATE(IF(AND(DAY('date')=29,MONTH('date')=2),DAY('date')-1,DAY('date'))&"/"&MONTH('date')&"/"&(YEAR('date')+21))

As before, you'll need to reorder the segments (underlined in the copy below) to match the numeric date format you use.

TEXTTODATE(IF(AND(DAY('date')=29,MONTH('date')=2),DAY('date')-1,DAY('date'))&"/"&MONTH('date')&"/"&(YEAR('date')+21))

Regards,
Barry
Barry
 
Posts: 1390
Joined: Mon Sep 30, 2002 12:54 am
Location: 48°50' N 123° 30'W


Return to AppleWorks Help

cron
AppleWorks Users Group Logo iWork Users Group Logo