How do I create a formula field that changes a date field to the the the current month?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Current date field:

Date: 10-5-2016

The formula field would then generate 12-5-2016 since the current month is December, each month the formula would update to the current month.

I tried using AdjustMonth() and Today() but was having no such luck.
Photo of Lori

Lori

  • 50 Points

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
no problem

Date(Year(Today()),Month(Today()),Day([$MyDate]))




so that will always have the year of the current year and the month of the current month, but the day number will float according to the date in your date field.
Photo of Lori

Lori

  • 50 Points
Perftect, thank you!
Photo of Roxanne

Roxanne

  • 122 Points 100 badge 2x thumb
What is the $Mydate field suppose to be? I am working on something similar - I want a today's date or current date  - I am trying to take a start date of employment  and to count how many years that person has worked from "today's date - so it will change based on Today's date
thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Here is an formula for AGE in full integer years.  So that would be completed full service years in your use case.



var date DOB = [DOB];

Year(Today())-Year($DOB)
-
If(
Month(Today())<Month($DOB)
or
(Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0)
Photo of Roxanne

Roxanne

  • 122 Points 100 badge 2x thumb
what is DOB?
Photo of Roxanne

Roxanne

  • 122 Points 100 badge 2x thumb
what is DOB?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
In my example it was a date field for the Date Of Birth.
Photo of Roxanne

Roxanne

  • 122 Points 100 badge 2x thumb
So I would just use my start date instead of DOB? so
var date DOB = [DOB];
would become
var date Startdate = [startdate]
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
yes, 
I suppose a better generic format would be this

var date MyDate = [Start Date];

Year(Today())-Year($MyDate)
-
If(
Month(Today())<Month($MyDate)
or
(Month(Today())=Month($MyDate) and Day(Today())<Day($MyDate)),1,0)
(Edited)
Photo of Roxanne

Roxanne

  • 122 Points 100 badge 2x thumb
comes up with a bunch of errors when I add it to the formula. $mydate seems to be a big problem

A variable declaration must end with a semi-colon.


var date MyDate = [Start of Employment]   Date];

Year(Today())-Year($MyDate DOB)
-
If(
Month(Today())<Month($MyDate DOB)
or

(Month(Today())=Month($MyDate) and Day(Today())<Day($MyDate)),1,0) 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Please post your formula, and not just the error message.
Photo of Roxanne

Roxanne

  • 122 Points 100 badge 2x thumb
I am trying to create the formula - the error message is from the previous post - I tried a formula above and received an error 
currently I am having no luck 
What i am trying to do in an employee data base - is have a field that counts the number of years since the start date of employment
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Please post your formula so i can correct it. 

I don't have time to re-type it, just copy and paste the formula from the formula box into this forum.