How would you input the current date into a exact form? I would want it in the format "4th day of month, year."

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
Photo of Luke

Luke

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Jesus

Jesus

  • 0 Points
You mean you'd like to input the data as "4th of July, 1776" and QB would display 4-7-1776 or you type 4-7-1776 and QB would display it as "4th of July, 1776"?
Photo of Luke

Luke

  • 0 Points
I would be pulling from a field that would have the 7-4-1776 format and I would like the exact form to switch it and display it as "4th of July, 1776"
Photo of Jesus

Jesus

  • 0 Points
You're going to need a Formula-Text 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 Formula-Text 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 Formula-Text 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!