Oct 03, 2016 03:47 AM
Hi,
I’m using Airtable to manage my library. I was hoping I could use it to automatically calculate the due date based on the date I loaned the book out. For example, if I loaned the book on the 5th of October, I want to add 14 days and have a column saying 19 October for due date.
How do I do this?
Regards
Oct 03, 2016 06:08 AM
Hi Kyle
You can create a formula field as follows:
DATEADD({Date 1}, 10, ‘days’)
(Where {Date 1} is the field to which you are adding the 10 days.
Note that this always results in a date formatted in US format (with the month first). If you want to format the date to suit your local taste the you can use something like:
DATETIME_FORMAT(DATEADD({Date 1}, 10, ‘days’), “DD-MM-YYYY”)
More information is in the Formula Field reference section in Help.
Another important note is that the result is a Text value rather than a date and so can’t be used to display things on a calendar View. [Airtable - it would be great to be able to specify a type for a formula field!].
Julian
Oct 03, 2016 06:54 AM
Thank you! It worked for me
Jan 07, 2017 11:02 PM
Kyle/Julian
I’m looking for a WORKDATE or similar function in airbase. I need to program in a TARGET DUE DATE for each client upon receipt of application to set their expectation. I did this using DATEADD function - worked great, but …
TARGET DUE DATE results are falling on Weekends and Holidays.
:frowning:
Any suggestions for functions or workarounds?
@Airtable_Support: Adding WORKDATE or similar function(s) will have huge appeal to anyone in project management (which lately seems to be everyone - one way or another)
Jan 08, 2017 02:48 AM
This is still possible without a Workdate function - it’s a matter of using If and Weekday() to determine which day of the week we are on and then adding the appropriate number of days. The example below adds two workdays to an entered date, for example:
IF(WEEKDAY({Date 1}) > 3, DATEADD({Date 1}, 4, ‘days’),IF(WEEKDAY({Date 1}) = 0, DATEADD({Date 1}, 3, ‘days’),DATEADD({Date 1}, 2, ‘days’)))
Note - WEEKDAY returns a 0 for Sunday up to a 6 on Saturday. So, this formula adds 4 days for a start date on Thursday, Friday or Saturday, 3 Days on a Sunday and 2 days on Monday, Tuesday or Wednesday.
Note this returns an American date format an if you want something else you can apply the formatting described above.
Hope this helps.
Julian
Jan 08, 2017 04:29 PM
Thanks Julian that’ll work!
Jan 15, 2017 08:32 PM
I’m thinking the formula you offered works if the number of work days being added crosses only 1 weekend. For each additional weekend crossed, the calculated date is 2 days less than what it should be. Any ideas?
Thanks
Phil
Jan 16, 2017 07:02 AM
Hi @Phil_Lauer - you are quite correct, the formula for any number of days added would be slightly different and require a modified formula - but structurally the formulae would be similar and would just need to account for the different additions for different starting days (Date 1). There are some simple cases as well - for example to add 5 working days you would always add 7 days - as with any multiple of 5 actually.
Jun 10, 2017 03:41 PM
Hi guys!
I have a datefield that I want to add to a number, which is another field that’s automatically populated from another table. For instance:
Date of Planting: 6/1/2017
Days to Maturity: 75
I want to add Planting + Maturity, but DATEADD doesn’t seem to allow an input other than an integer in the COUNT section. I.e., I want to do this;
DATEADD({Date of Planting},{Days to Maturity},‘days’)
With an output of ‘08/15/2017’
But it’s not working!!! I can do this function very easily in Excel, please help!!!
Thank you! :slightly_smiling_face:
Jun 12, 2017 11:27 AM
From your description, it sounds like the {Days to Maturity} field is a Lookup of a number field.
Because Lookups can contain multiple linked values, in formulas they’re represented as an array. Sometimes Airtable’s formula engine will automatically recognize and convert these arrays, but in other cases like DATEADD you have to manually convert from an array to a single value. ARRAYJOIN will convert from an array to text, and VALUE will convert from text to number.
Try this modification:
DATEADD({Date of Planting},VALUE(ARRAYJOIN({Days to Maturity})),'days')
Jun 13, 2017 09:08 AM
Matt Bush, you are my hero! :heart_eyes: Thanks for the help, that worked perfectly!!
Jul 11, 2017 09:08 PM
Any updates on if they’ve added a workday function? I’m hoping to use for project management in calculating deadlines automatically.
Aug 30, 2017 10:13 AM
Plus 1 for this feature. Real Estate contracts- I need to add a certain number of days to a date, and they cannot END on a weekend or holiday. There can be weekends and holidays in the middle, but it cannot end on one. Would be huge for me, and the millions of people (lender/title/escrow/realtors) in the industry.
Conversely, I also need to be able to subtract a certain number of days from the end date. Should be easy once the above is implemented.
Aug 30, 2017 01:55 PM
Happy to share that we now have a WORKDAY()
function!
The format is WORKDAY(startDate, numDays, [holidays])
and it returns a date that is numDays
working days after startDate
. Working days automatically exclude weekends, and you can include an an optional list of holidays as a comma-separated string of ISO-formatted dates, e.g.
WORKDAY({Launch date}, 100, '2017-09-04, 2017-10-09, 2017-11-10')
@Valerie_Miles
For real estate contracts, you should now be able to use a formula similar to this:
WORKDAY(DATEADD({Input Date},{Days to Add or Subtract}-1,'days'),1,'2017-09-04')
Let me know if this works!
Sep 28, 2017 03:10 PM
I’m new to AirTable and trying to create a formula for start date. We have a date field for due date and a number field for lead time. I’d like to create a formula that takes the due date subtracts the number of lead time days to give us a start date. Any help would be appreciated.
Sep 28, 2017 06:40 PM
Pretty much just as you describe it:
DATEADD({Due Date},-{Lead Time},'day')
If you need business day, try
WORKDAY({Due Date},-{Lead},[optional list of holidays])
Oct 29, 2017 05:35 AM
hi julian!
this is exactly my problem: i have a start date and the due date is calculated with dateadd. but in calendar view it’s impossible to set the ‘end date field’ based on those fields. do you know of a workaround? thanks!
Oct 29, 2017 05:55 AM
I’ve just successfully set up a calendar view with an entered field and a formula field and it worked fine - you do have to format the formula field though.
Mar 22, 2018 02:46 PM
@Julian_Kirkness and @Matt_Bush
I am trying to set up a project and want to use duration (instead of “date”) as a field to set up the schedule. Right now I am manually entering “due date” and then have a formulation for duration days as reference, but I’d like to have it vice versa- where I enter the duration days and the due date will be populated. Is there any way I can do that?
Also, Is there any field where I can link each different tasks as predecessors- such as certain task B cannot start until task A is complete. Any feedback or input you can provide is highly appreciated!
Apr 06, 2018 04:53 PM
I’m looking for the same thing. My company, a wholesale greenhouse uses “crop times” to calculate ship dates in our production system. I want to set up a similar system for my home garden. A planting date + a crop time (a number of days) = a projected harvest date. This would be pretty handy. In our database at work the dates get converted to week numbers so it is easier to calculate. I might just do a similar solution so I can just do it with integers.