Help

Rounding a date to the half year

Topic Labels: Formulas
Solved
Jump to Solution
843 5
cancel
Showing results for 
Search instead for 
Did you mean: 
ML
4 - Data Explorer
4 - Data Explorer

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!

2 Solutions

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

kuovonne_0-1688427614687.png

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.)
kuovonne_1-1688427641732.png

See Solution in Thread

Sho
11 - Venus
11 - Venus

Hello @ML ,

This formula may also be fine.

DATEADD(
  DATESTR("7/1/"&YEAR({Date})),
  IF(MONTH({Date})>=7,18,12),
  "month"
)

 

See Solution in Thread

5 Replies 5
kuovonne
18 - Pluto
18 - Pluto

kuovonne_0-1688427614687.png

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.)
kuovonne_1-1688427641732.png

Sho
11 - Venus
11 - Venus

Hello @ML ,

This formula may also be fine.

DATEADD(
  DATESTR("7/1/"&YEAR({Date})),
  IF(MONTH({Date})>=7,18,12),
  "month"
)

 

ML
4 - Data Explorer
4 - Data Explorer

Awesome thank you! They both do the job, though I'm still trying to figure out how @Sho 's solution works 🙂


@ML wrote:

I'm still trying to figure out how @Sho 's solution works 🙂


It's a neat solution. I added a discussion of it to the page I linked to in my previous post.

Sho
11 - Venus
11 - Venus

@kuovonne Excellent explanation!
I agree that using Datetime_parse is a sure thing