Apr 10, 2018 12:58 PM
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!
Apr 10, 2018 01:37 PM
This formula will add 90 days,
DATEADD({Date Updated},90,‘day’)
Support link:
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:
Apr 10, 2018 02:28 PM
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 )
Apr 10, 2018 02:57 PM
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!
Apr 10, 2018 02:58 PM
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?
Apr 10, 2018 03:28 PM
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.
Apr 11, 2018 12:36 AM
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:
Apr 11, 2018 10:07 AM
THANK YOU! I really appreciate the help. Loving Airtable and understanding these formulas will be amazing for functionality.