I'd like a field to look at a specific date entered, and then return the year/month in the following format: YYYYMM, where MM would be twodigit rendering of the month. For example, 1/15/14 would return 201401, or 11/2/13 would return 201311.
The following formula almost gets me there  except the month shows as a single digit, instead of having a leading 0 when needed.
var number YYYY = Year([SLA Date:]);var number MM = Month([SLA Date:]);(ToText($YYYY)) & "" & (ToText($MM))
The following formula almost gets me there  except the month shows as a single digit, instead of having a leading 0 when needed.
var number YYYY = Year([SLA Date:]);var number MM = Month([SLA Date:]);(ToText($YYYY)) & "" & (ToText($MM))
 10 Points
Posted 6 years ago
 700 Points
No problem, Try this
var text YYYY = Totext(Year([SLA Date:]));
var text MM = right("0" & totext(Month([SLA Date:]),2);
$YYYY & "" & $MM)
This trick is to zero pad the month, so you stick on a leading zero and then take the rightmost 2 characters.
so, 0 becomes 01 and 12 becomes 012 which gets trimmed to 12 when you take the rightmost 2 characters.
var text YYYY = Totext(Year([SLA Date:]));
var text MM = right("0" & totext(Month([SLA Date:]),2);
$YYYY & "" & $MM)
This trick is to zero pad the month, so you stick on a leading zero and then take the rightmost 2 characters.
so, 0 becomes 01 and 12 becomes 012 which gets trimmed to 12 when you take the rightmost 2 characters.
 0 Points
The "trick is to zero pad the month"! I almost gave up in my search for the answer until I stumbled upon this  thanks Mark!
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 72,216 Points
:) glad my posting from 2 years ago was useful. I remember the first time someone on this forum showed me how to do that, so I'm just keeping the knowledge alive.
 422 Points
Yes, this is awesome... I ended up using two of your answers to get what I needed  YYMMDD
Here's what I have:
var date MyDate = ToDate([Date Created]);
var text MM = Right("0" & ToText(Month(ToDate([Date Created]))),2);
var text DD = Right("0" & ToText(Day($MyDate)),2);
var text YY = Right(ToText(Year($MyDate)),2);
$YY & $MM & $DD
Here's what I have:
var date MyDate = ToDate([Date Created]);
var text MM = Right("0" & ToText(Month(ToDate([Date Created]))),2);
var text DD = Right("0" & ToText(Day($MyDate)),2);
var text YY = Right(ToText(Year($MyDate)),2);
$YY & $MM & $DD
Brian Cafferelli, Quick Base Technical Marketing Manager
 1,628 Points
Fun fact: you can use our new PadLeft and PadRight formulas to make sure you have the right number of 0's for your formula. So you could write the formula this way:
var date MyDate = ToDate([Date Created]);
var text MM = PadLeft(ToText(Month(ToDate([Date Created]))),2,"0");
var text DD = PadLeft(ToText(Day($MyDate)),2,"0");
var text YY = PadLeft(ToText(Year($MyDate)),2,"0");
$YY & $MM & $DD
More details in the Formula Functions Reference: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=191&rl=i7
var date MyDate = ToDate([Date Created]);
var text MM = PadLeft(ToText(Month(ToDate([Date Created]))),2,"0");
var text DD = PadLeft(ToText(Day($MyDate)),2,"0");
var text YY = PadLeft(ToText(Year($MyDate)),2,"0");
$YY & $MM & $DD
More details in the Formula Functions Reference: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=191&rl=i7
Related Categories

Forms
 3347 Conversations
 150 Followers

Formulas & functions
 3044 Conversations
 82 Followers

Tables & fields
 7332 Conversations
 200 Followers