Skip to main content

Using text in other field as a formula

  • April 18, 2022
  • 1 reply
  • 19 views
N
Forum|alt.badge.img

Hi there,
Im a beginner of Airtable living in Japan. Im about creating DB in airtable for generating invoices automatically but there are some problems. My english is not good so if I didnt make any sense, just tell me.
I want to calculate datetime data with a fomura in other field linked to another record like below, however it doesnt work.

DATETIME_FORMAT(DATEADD(DATEADD({FieldName},2,‘month’),-DAY(DATEADD({FieldName},1,‘month’)),‘days’),‘YYYY/MM/DD’)

I dont have any ideas about this. Please tell me if there are any knowledge.
Best Regards,

1 reply

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • April 18, 2022

Hey @najacc_NAJ!
Welcome in!

I’m taking a look at your provided formula, and whilst I can clean it up to satisfy the syntax, I’d like to know a bit more about your use case since I’m a bit confused about what you’re looking to accomplish.

Even though the formula I provided down below is technically formatted, it might not provide you with the desired result.

If you could hit me with the story of what you’re looking to achieve, it would be easier to troubleshoot and solution.

From what I can tell from the formula, you’re looking to:

  1. Take a Date/Time, and add two months to the value.
  2. Find the day of the month from the (original) Date/Time with one month to it.
  3. Add the day of the month value to the first two-month calculation.
  4. Format that in YYYY/MM/DD.

This is the formula that accomplishes that:

DATETIME_FORMAT(
    DATEADD(
        DATEADD(
            {FieldName},
            2,
            'month'
        ),
        DAY(
            DATEADD(
                {FieldName},
                1,
                'month'
            )
        ),
        'days'
    ),
    'YYYY/MM/DD'
)

Here’s the original formula that you posted.

1 DATETIME_FORMAT(
2    DATEADD(
3       'DATEADD(
4            {FieldName},
5            2,
6            ‘month’
7        ),
8        -
9        DAY(
10            DATEADD(
11                {FieldName},
12                1,
13                ‘month’
14            )
15        ),
16        ‘days’
17    ),
18    ‘YYYY/MM/DD’
19 )

On line 8, there is a -, I’m curious if you’re actually looking to subtract the value of the date?
If so, let me know and we can tweak it around.

Regardless, if something seems weird, if I’m misunderstanding something, if it doesn’t work, or if you have any questions, let me know!

Terms & ConditionsAccessibility statement