Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Specific Formulas

Rounding a date to the half year

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Inspiration

this community post⁠

, the user wants to add one year to a date, 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.

Formula Result

This screen capture shows my formula result. Note that my formula “rounds up” January 1 and July 1 to the next half year. It isn’t clear if they should be or not. Not rounding up those two dates is doable, but a more complex formula.

⁠

Discussion

When adding a specific amount of time to a date, I use DATEADD(). For example to add a year to a date, I could use DATEADD(Date, 1, "year").

However, this use case doesn’t involve adding a specific amount of time, so I reach for DATETIME_PARSE() instead. For example, we could get January 1 of the following year with this formula. Note that I have to put parenthesis around the sum so it is calculated before being combined with the text.

DATETIME_PARSE(

"1/1/" & (YEAR(Date) + 1),

"M/D/YYYY"

)

But we don’t always want January 1. Sometimes we want July 1. If the current month is January - June, we want July 1. If the current month is July - December, we want January 1. Because the formula is getting a bit more complicated, let’s also use CONCATENATE() instead of &.

DATETIME_PARSE(

CONCATENATE(

IF(MONTH(Date) < 7, 7, 1),

"/1/",

YEAR(Date) + 1

"M/D/YYYY"

)

So we’re getting closer, but the year isn’t quite right yet. If the date is in the second half of the year, we need January two years later, not just one year later.

DATETIME_PARSE(

CONCATENATE(

IF(MONTH(Date) < 7, 7, 1),

"/1/",

IF(

MONTH(Date) < 7,

YEAR(Date) + 1,

YEAR(Date) + 2

)

"M/D/YYYY"

)

Notice that the function repeats the same IF() condition. Let’s move that IF() to the outside and return to using & instead of CONCATENATE().

IF(

MONTH(Date) < 7,

DATETIME_PARSE(

"7/1/" & (YEAR(Date) + 1),

"M/D/YYYY"

DATETIME_PARSE(

"1/1/" & (YEAR(Date) + 2),

"M/D/YYYY"

)

But that has two DATETIME_PARSE() functions. Let’s make this even cleaner.

DATETIME_PARSE(

IF(

MONTH(Date) < 7,

"7/1/" & (YEAR(Date) + 1),

"1/1/" & (YEAR(Date) + 2)

"M/D/YYYY"

)

Followup Discussion

After I came up with the above solution, another user

Sho⁠

, posted the following alternative formula with a totally different approach to the same issue.

DATEADD(

DATESTR("7/1/"&YEAR({Date})),

IF(MONTH({Date})>=7,18,12),

"month"

)

I like to see formulas that other people produce because I can learn from them. It is like looking at a work of art from a new angle that I hadn’t considered before. The original poster liked the formula, but had questions about how it worked, so I decided to add an explanation here.

Starting with July 1

This formula starts with a text string for July 1 of the current year: "7/1/"&YEAR({Date}). Then the formula converts the text string to a different date format with DATESTR(). Note that Airtable does a bit of data type manipulation behind the scenes. Both "7/1/"&YEAR({Date}) and DATESTR("7/1/"&YEAR({Date})) produce text strings that look like dates, and Airtable conveniently converts those text strings to dates.

If you write dates the European way, you might think that the date looks like the 7th of January, instead of July 1st, but Airtable use the USA conventions for dates.

Adding Months

Then the formula adds either 18 or 12 months to July 1 of the current year using DATEADD(). If the month is in the second half of the year (MONTH({Date})>=7, the formula adds 18 months (a year and a half). Otherwise the formula adds 12 months (a year).

But why add 12 or 18 months to July 1 of the current year?

For dates in the first half of the year, you want to add a year, and then advance to July 1. This formula does these two operations in the opposite order. The formula advances to July 1 of the current year, and then adds a year.

For dates in the second half of the year, you want to add a year, and then advance to January 1. This formula backs up to to July 1 of the current year, and then adds a year and a half. Backing up to July 1 and then adding that additional half a year has the same effect as advancing to January 1.

Yet Another Version of the Formula

I decided to experiment with the formula a bit.

I removed the DATESTR() function since it didn’t do much.

I switched the >= comparison to < because it is easier for me to think about the first half of the year first, and the second half of the year second.

I added a little more white space.

DATEADD(

"7/1/" & YEAR({Date}),

IF(MONTH({Date}) < 7, 12, 18),

"month"

)

Although creating this version of the formula was an interesting thought exercise, I don’t actually like this version of the formula.

This version depends on Airtable converting the text string to a date object. I prefer to explicitly convert text to dates using DATETIME_PARSE(). While I usually like to let Airtable take care of data type conversions for me, I don’t like doing it for dates, in part because different parts of the world write dates differently.

I find the logic to be a little hard to follow.

Choosing a Formula Version

When multiple different formulas produce the correct result, I always recommend picking the version that you understand best. Sometimes that means picking a formula that is not as efficient or that has more lines of code. That is okay.

Some day you may need to revisit your formula, to enhance what it does, to address a new use case, or to simply figure out what is going on. When that happens, you will be better off with the formula that you understand.

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.