date based on numeric month

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
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.
Photo of Dante Tognoli

Dante Tognoli

  • 406 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of Dante Tognoli

Dante Tognoli

  • 406 Points 250 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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)
Photo of Dante Tognoli

Dante Tognoli

  • 406 Points 250 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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)
Photo of Dante Tognoli

Dante Tognoli

  • 406 Points 250 badge 2x thumb
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.