Help

Re: Can't get formula to work - what am I doing wrong?

954 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jeannie_Ruesch
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi - I’ve tried a number of formulas I’ve found in the support and can’t seem to get them to work. Positive it’s user error and since I’m not sure what I’m doing wrong, I thought I’d present what I’m looking to do and get a formula to copy/paste.

I have a column called “Date Updated” (in date format) – and the column next to it, where I want the formula to populate, is for “Next Review”. This should equal the date in the Date Updated column + 90 workdays. How would I create that formula?

Appreciate the help!

7 Replies 7
Miroslav_Tunjic
6 - Interface Innovator
6 - Interface Innovator

This formula will add 90 days,
DATEADD({Date Updated},90,‘day’)

Support link:

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

BR

:m:

The formula @Miroslav_Tunjic provided will give you a date 90 calendar days from {Date Updated} — and, typically, a 90-day shift is usually measured in calendar days. If you truly want a date 90 workdays (means Monday through Friday, barring holidays), you’d want to use the WORKDAY(() function:

WORKDAY({Date Updated},90,[{Holidays}])

where {Holidays} is an

optional list of holidays, formatted as a comma-separated string of ISO-formatted dates.

For instance,

WORKDAY({Date Updated},90,'2018-01-01, 2018-01-15, … and so on )

Thank you - that worked. I realized I had to retype the ’ as well - as they didn’t work with a straight copy/paste. So that might have been part of my issue.

One additional question if I may: The cell is completing as date and time, so it looks like:

2/13/2018 12:00 am

How would I alter this to remove the time? Thank you so much for the help!

Thank you for this clarification as well.
So I would need to add in those holiday dates myself in the format below (year first), for this to work?

Well, it depends on how much a stickler for detail you are: If you know your date+90 workdays will not land on a holiday. and you aren’t that concerned there might be 89- or 88-workday interval, instead of a definite 90 day one, you can forego the string of holidays. (You can also dispense with any holidays that fall on a weekend, obviously.) As you note, they need to be in ISO date standard, which is 4-digit year, 2-digit month, and 2-digit day, with hyphens separating the year, month, and day. (Note that when I say ‘2-digit month,’ I mean just that: single-digit months and days need to be left-padded with a zero.)

As far as removing the time field from your calculated date/time field, right-click on the field header, select ‘Change field configuration,’ selecting the ‘formatting’ tab, and toggle off ‘Include a time field.’ This would also be a good time to check the status of your GMT toggles: Every date field (including calculated dates, as well as dates accessed from a link record using a rollup or lookup field) has a toggle as to whether or not to use GMT for all collaborators. That doesn’t mean you enter your local time in the field, and the field normalizes it to GMT; instead,. it means that should you enter a time value in a date field, Airtable will assume you’re specifying it in GMT.

None of this would matter, except should you have one date field set to use GMT and another not to, you’ll start seeing odd discrepancies. Usually it manifests itself as a day count incrementing too quickly. It doesn’t matter that much which way you have your fields set; what’s important is they all be set the same way.

Miroslav_Tunjic
6 - Interface Innovator
6 - Interface Innovator

@Jeannie_Ruesch

as @W_Vann_Hall said:
"Right-click on the field head, select ‘Customize field type,’ select the ‘Formatting’ tab, and choose which date or date-and-time format you desire. :winking_face: "

:winking_face:

Jeannie_Ruesch
5 - Automation Enthusiast
5 - Automation Enthusiast

THANK YOU! I really appreciate the help. Loving Airtable and understanding these formulas will be amazing for functionality.