Hi, can someone help me with a formula?
This is what I have now:
DATEADD(DATESTR(“2021-04-25”),-{Indoors Weeks Prior},‘week’)
I want to add something so that if nothing is in the Indoors Weeks Prior column, it will show nothing.
How do I do that?
Also, currently, it displays DATE and then time. How can I omit the time part?
Hi and welcome @Whitney_Anderson1!
You can do this by wrapping your formula in an IF()-statement.
This you address by using DATETIME_FORMAT()
This should do what you want it to - and illustrate the use of IF() and DATETIME_FORMAT().
IF(
{Indoors Weeks Prior},
DATETIME_FORMAT(
DATEADD(
'2021-04-25',
-{Indoors Weeks Prior},
'week'
),
'YYYY-MM-DD'
),
''
)
You can find a realy handy formula field reference guide here.
Hi and welcome @Whitney_Anderson1!
You can do this by wrapping your formula in an IF()-statement.
This you address by using DATETIME_FORMAT()
This should do what you want it to - and illustrate the use of IF() and DATETIME_FORMAT().
IF(
{Indoors Weeks Prior},
DATETIME_FORMAT(
DATEADD(
'2021-04-25',
-{Indoors Weeks Prior},
'week'
),
'YYYY-MM-DD'
),
''
)
You can find a realy handy formula field reference guide here.
You’re pretty close, but there are some errors in your answer.
I believe that Whitney is asking about the way that the field displays the result with both date and time visible. This is controlled in the Formatting tab in the field’s setup dialog. Double-click the field header to open this setup dialog, and you’ll see the Formatting tab.
Looking at your formula, the data you’re feeding the DATEADD() function isn’t correct. The first argument needs to be an actual datetime; i.e. a data value representing a date/time combination. What you’re giving it (as far as Airtable is concerned) is just a string containing numbers and hyphens. It doesn’t auto-convert strings into dates.
If the desired date is in another field, then that first argument can be a reference to that field:
...
DATEADD(
{Start Date},
-{Indoors Weeks Prior},
...
If the start date is a literal date that will always be the same for every record, you can take that string and parse it using DATETIME_PARSE():
...
DATEADD(
DATETIME_PARSE(
'2021-04-25',
'YYYY-MM-DD'
),
-{Indoors Weeks Prior},
...
Sometimes DATETIME_PARSE() can parse a date string without needing the format string as a second argument, but I included it just in case.
The use of the IF() function that @momentsgoneby80 demonstrated will work, but it’s also worth noting that the third argument in the IF() function is optional. If omitted, the function will automatically leave the field blank, so there’s no need to include the empty string. In fact, adding an empty string like that can actually cause problems in certain cases because it forces the output from the function to always be a string, even if you want the output from the second argument—the “result if true” portion—to be a different type like a number or date. I prefer to leave out that third argument unless it’s necessary to return some other value if the first argument is false.
Hi and welcome @Whitney_Anderson1!
You can do this by wrapping your formula in an IF()-statement.
This you address by using DATETIME_FORMAT()
This should do what you want it to - and illustrate the use of IF() and DATETIME_FORMAT().
IF(
{Indoors Weeks Prior},
DATETIME_FORMAT(
DATEADD(
'2021-04-25',
-{Indoors Weeks Prior},
'week'
),
'YYYY-MM-DD'
),
''
)
You can find a realy handy formula field reference guide here.
This worked perfectly. Thank you so much!
You’re pretty close, but there are some errors in your answer.
I believe that Whitney is asking about the way that the field displays the result with both date and time visible. This is controlled in the Formatting tab in the field’s setup dialog. Double-click the field header to open this setup dialog, and you’ll see the Formatting tab.
Looking at your formula, the data you’re feeding the DATEADD() function isn’t correct. The first argument needs to be an actual datetime; i.e. a data value representing a date/time combination. What you’re giving it (as far as Airtable is concerned) is just a string containing numbers and hyphens. It doesn’t auto-convert strings into dates.
If the desired date is in another field, then that first argument can be a reference to that field:
...
DATEADD(
{Start Date},
-{Indoors Weeks Prior},
...
If the start date is a literal date that will always be the same for every record, you can take that string and parse it using DATETIME_PARSE():
...
DATEADD(
DATETIME_PARSE(
'2021-04-25',
'YYYY-MM-DD'
),
-{Indoors Weeks Prior},
...
Sometimes DATETIME_PARSE() can parse a date string without needing the format string as a second argument, but I included it just in case.
The use of the IF() function that @momentsgoneby80 demonstrated will work, but it’s also worth noting that the third argument in the IF() function is optional. If omitted, the function will automatically leave the field blank, so there’s no need to include the empty string. In fact, adding an empty string like that can actually cause problems in certain cases because it forces the output from the function to always be a string, even if you want the output from the second argument—the “result if true” portion—to be a different type like a number or date. I prefer to leave out that third argument unless it’s necessary to return some other value if the first argument is false.
Thank you so much for this detailed explanation.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.