# Discussions

View Only

## date based on numeric month

• #### 1.  date based on numeric month

Posted 06-21-2018 20:13
I am trying to calculate a date based on a month. We have certifications that expire not based on the date that the certificate was issued, but on a specific month of the year. This is called a "base month" if a certificate expires after one year and is given in march but the base month is February I need to generate an expiration date based on February (so given 3/15/18 returns 02/01/19). I was planning on converting the base month to it's corresponding number (jan=1, etc...) but I'm not sure how calculate a date based on a single number.

• #### 2.  RE: date based on numeric month

Posted 06-23-2018 04:15
So I got the function working, but it's messy and spans multiple fields each referencing the previous. Is there a way to put all of these formulas together into a single field?

• #### 3.  RE: date based on numeric month

Top
Contributor
Posted 06-25-2018 13:33
If you find yourself needing to make intermediate fields to do a stepwise calculation, you can instead use formula variables.  https://help.quickbase.com/user-assistance/formula_variables.html

for example here is how they could be used to do your calculation

var date SameDayLastYear = AdjustYear([Certification Date],-1);

\$SameDayLastYear - Days(15)

• #### 4.  RE: date based on numeric month

Posted 06-25-2018 16:01
Thanks, that looks a lot nicer than what I came up with!  The formula variables look like they will be very useful with some of the other tasks I need to do.

• #### 5.  RE: date based on numeric month

Top
Contributor
Posted 06-25-2018 16:04
Writing complex formuals in a stupid simple way is worthwhile as you may be the poor soul who 5 years from now needs to understand a formula ( and you may have been the one to write it).

Also using comments is useful.  A comment is a line or part of a line beginning with  //

for example

// first calculate last year same day.
var date SameDayLastYear = AdjustYear([Certification Date],-1);

// then we subtract 15 days.
\$SameDayLastYear - Days(15)

• #### 6.  RE: date based on numeric month

Posted 06-25-2018 16:13
I've been using comments (I have an incredibly meager amount of experience with java and robotC) which helps me keep track of what is going on, but the biggest problem I am having now is defining exactly what I want a formula or series of formulas to do and determining the best course of action to solve the problem. Now that I have the creation of expiration dates working I need to go back and compare multiple records for a single person that may or may not have overlapping dates and then determine if all of their certificates are valid. Right now we are doing this by hand, but it leads to human error mistakes . I would like to automate it, but i'm not sure how to even define exactly what I am doing.