 0 Points
Posted 4 years ago
 0 Points
You mean you'd like to input the data as "4th of July, 1776" and QB would display 471776 or you type 471776 and QB would display it as "4th of July, 1776"?
 0 Points
I would be pulling from a field that would have the 741776 format and I would like the exact form to switch it and display it as "4th of July, 1776"
 0 Points
You're going to need a FormulaText and your Date field of course.
I recommend creating 3 variables, we're going to call them VarDay, VarMonth, VarYear for example.
Since it's a date we need to convert it to text and then separate it into 3 parts using the Part function:
VarDay = ToNumber(Part(ToText([YourDateField]), 2, "")) //Look into YourDateField and give me the second part as delimited by a dash ""
VarMonth = ToNumber(Part(ToText([YourDateField]), 1, ""))
VarYear = ToNumber(Part(ToText([YourDateField]), 3, ""))
What each one does is taking the Date entry, converting it to text because Part() works with text only, then selecting each part individually according to what the user needs (in this case the dash), then getting that portion and converting it to number.
In the FormulaText field, we need to start formatting and using conditions so QB knows how everything is going to be placed.
First with the day:
ToText([VarDay]) & Case([VarDay], 1 or 21 or 31, "st", 2 or 22, "nd", 3 or 23, "rd", "th")
With this function we are determining how to properly format the number to 1st, 2nd, 3rd, 4th, etc.
Next for the month we do the same. Since we don't need the number, we don't have to convert it to text.
Case([VarMonth], 1, "January", 2, "February", 3, "March") //And so on after december. Since the month will always be true we don't need a reminding case like we did with "th" in the day function.
And finally since we don't do anything special with the year, we just need to call it and convert it to text:
ToText([VarYear])
Now we need to add the finishing touches and have the completed formula in the FormulaText field:
ToText([VarDay]) & Case([VarDay], 1 or 21 or 31, "st", 2 or 22, "nd", 3 or 23, "rd", "th") & " of " & Case([VarMonth], 1, "January", 2, "February", 3, "March") & ", " & ToText([VarYear])
This will convert any numerical date into the format you're looking for.
Hope it helped!
I recommend creating 3 variables, we're going to call them VarDay, VarMonth, VarYear for example.
Since it's a date we need to convert it to text and then separate it into 3 parts using the Part function:
VarDay = ToNumber(Part(ToText([YourDateField]), 2, "")) //Look into YourDateField and give me the second part as delimited by a dash ""
VarMonth = ToNumber(Part(ToText([YourDateField]), 1, ""))
VarYear = ToNumber(Part(ToText([YourDateField]), 3, ""))
What each one does is taking the Date entry, converting it to text because Part() works with text only, then selecting each part individually according to what the user needs (in this case the dash), then getting that portion and converting it to number.
In the FormulaText field, we need to start formatting and using conditions so QB knows how everything is going to be placed.
First with the day:
ToText([VarDay]) & Case([VarDay], 1 or 21 or 31, "st", 2 or 22, "nd", 3 or 23, "rd", "th")
With this function we are determining how to properly format the number to 1st, 2nd, 3rd, 4th, etc.
Next for the month we do the same. Since we don't need the number, we don't have to convert it to text.
Case([VarMonth], 1, "January", 2, "February", 3, "March") //And so on after december. Since the month will always be true we don't need a reminding case like we did with "th" in the day function.
And finally since we don't do anything special with the year, we just need to call it and convert it to text:
ToText([VarYear])
Now we need to add the finishing touches and have the completed formula in the FormulaText field:
ToText([VarDay]) & Case([VarDay], 1 or 21 or 31, "st", 2 or 22, "nd", 3 or 23, "rd", "th") & " of " & Case([VarMonth], 1, "January", 2, "February", 3, "March") & ", " & ToText([VarYear])
This will convert any numerical date into the format you're looking for.
Hope it helped!
Related Categories

Forms
 3342 Conversations
 148 Followers

Tables & fields
 7324 Conversations
 199 Followers