7 Replies Latest reply on Jul 13, 2017 5:31 PM by fitch

# calculation to change date x number of weeks based on today

I have a service company. We use a frequency drop down currently with months. I have a calculation that takes the completed service date and adds the frequency of months to give me a next service date.

How do I change this frequency to be days or weeks?

My current calculation is -

Date (Month ( Previous Service Date ) + Service Frequency ; Day ( Previous Service Date ) ; Year ( Previous Service Date ))

• ###### 1. Re: calculation to change date x number of weeks based on today

Are you saying you want to be able to choose days, weeks, or months? If so, you'd need one field for the number and another for the type (days, weeks, months).

Let( d = Previous Service Date ; // just for ease of reading

Case(

type = "months" ; Date (Month ( d ) + Service Frequency ; Day ( d ) ; Year ( d )) ;

type = "weeks" ; d + (Service Frequency * 7) ;

d + Service Frequency )  // defaulting to days

)

)

• ###### 2. Re: calculation to change date x number of weeks based on today

one option is to use intermediate calculation fields to convert all available frequencies to a standard unit of time such as days  then add to the LastService Field.    and example could  be the following

number of units            Units (drop down menu)            Calculation (Let function make it easier)

1                                   week                                           If ( Unit= week;  NumberofUnits * 7 ; if Unit=Days ..... )

• ###### 3. Re: calculation to change date x number of weeks based on today

I would be fine just switching my calc to be weeks only. Just unsure how.

• ###### 4. Re: calculation to change date x number of weeks based on today

Keeping your current calculation and If FrequencyField =  a number of weeks

Date (Month (LastService ) ; Day ( LastService ) + (WeeklyFreq*7) ; Year ( LastService ))

• ###### 5. Re: calculation to change date x number of weeks based on today

Then you just need this part of Tom's suggested calculation:

d + (Service Frequency * 7)

Where D is your previous service date.

• ###### 6. Re: calculation to change date x number of weeks based on today

Nice.

• ###### 7. Re: calculation to change date x number of weeks based on today

Adding days in FileMaker uses simple addition (e.g., date + 30). Your month calc was a little more complex because months have varying lengths, but just adding days or weeks is straightforward addition.

Adding a week just means adding 7 days. So when you select a number of weeks, you multiply that by 7:

Previous Service Date + (Service Frequency * 7)

Now, if you want to convert your existing records, changing monthly to weekly, you could assume say 4 weeks to a month, and use the Replace command with the calculation:

Service Frequency * 4

(Warning: make a backup, you can't undo the Replace!)