- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 03, 2023 04:18 PM
Hello! I'm new to Airtable and trying to create a formula field based on a date field. The formula should add 1 year to the date (the easy part), then round up to the half year beginning January 1 or July 1. For example, 5/15/23 would calculate 7/1/24; and 10/2/23 would calculate 1/1/25. Any and all help is appreciated!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 03, 2023 04:43 PM - edited ‎Jul 03, 2023 05:18 PM
You can see my thought process for coming up with the formula here in Kuovonne's Guide to Airtable.
Here is an early version of the formula. (For a better version of the formula, use the link to my guide.)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 03, 2023 06:53 PM
Hello @ML ,
This formula may also be fine.
DATEADD(
DATESTR("7/1/"&YEAR({Date})),
IF(MONTH({Date})>=7,18,12),
"month"
)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 03, 2023 04:43 PM - edited ‎Jul 03, 2023 05:18 PM
You can see my thought process for coming up with the formula here in Kuovonne's Guide to Airtable.
Here is an early version of the formula. (For a better version of the formula, use the link to my guide.)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 03, 2023 06:53 PM
Hello @ML ,
This formula may also be fine.
DATEADD(
DATESTR("7/1/"&YEAR({Date})),
IF(MONTH({Date})>=7,18,12),
"month"
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 04, 2023 08:30 AM
Awesome thank you! They both do the job, though I'm still trying to figure out how @Sho 's solution works 🙂

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 04, 2023 03:14 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jul 04, 2023 05:58 PM
@kuovonne Excellent explanation!
I agree that using Datetime_parse is a sure thing
