Help CONCAT Date/Time and Currency Fields

Ask for Help
1

I still have a need to create a form in Excel with my Grist data, and I’m trying to make it as seamless a process as possible; creating my Grist data to export as I wish it to appear on the form.

I have two concatenation formulas that I can’t seem to get right. The first is a simple combination of two Date/Time fields. I end up with an extra 7:00 that I think has to do with time zone but I don’t know how to make it go away. I would rather it say AM/PM. Also, I’d like it to keep the original Date format of MM/DD/YY.
image

"{}→, {}".format($TravelTo_Start,$TravelTo_End)

image

The second problem is joining 3 fields, one is a currency field but returns just a number. I’ve added a ‘$’ to the formula, but still have the dropped ‘0’.

"{} {} - ${}".format($LaborType.MatCode,$LaborDesc,$LaborType_Price)

image
I played with using a LookupOne (in various ways…)for the price but couldn’t make it work.
$LaborType_Price is an additional lookup field brought in with ‘Materials’ referenced table.

I’ve been playing with this all week & any help would be greatly appreciated!!

2

Let me start with lookupOne: it’s documented here lookupOne. Perhaps you could share what you tried and what isn’t working?

As for formatting, Python supports various options inside the curly braces for formatting dates and numbers. I encourage you to try the AI Assistant for such things, because this is a sort of thing it understands pretty well. But here are some pointers:

  • To include two decimal places after decimal point:
    "{:.2f}".format(187.5)
  • To format date as MM/DD/YY:
    "{:%m/%d/%y}".format(datetime.datetime.now())
  • To format date and time without a time zone:
    "{:%m/%d/%y %-I:%M%p}".format(datetime.datetime.now())
  • You can combine such format specifiers, e.g. "{:%m/%d/%Y} {%.2f}".format(some_date, some_number)
1 Like
3

FANTASTIC!!! :raised_hands: What a relief to have these resolved!
I didn’t need to tackle trying to make the LookupOne work as the “{} - ${:.2f}” gave me just what I need.
For the date concatenation, I added an extra space for the AM/PM

"{:%m/%d/%y %-I:%M %p}→, {:%m/%d/%y %-I:%M %p}".format($TravelTo_Start,$TravelTo_End)

and modified it to show blank when I had no times:

if not $Return_Start or not $Return_End:
  return ""
else:
  return "{:%m/%d/%y %-I:%M %p}→, {:%m/%d/%y %-I:%M %p}".format($Return_Start,$Return_End)

I am constantly amazed at what Grist can do with my data! Thank you so much for the solutions!!