Simple date calculation

[WilliamPorter]

This ought to be really simple but I cannot suss it. I’ve looked at the help files for functions and also for nested if statements and no doubt the answer is in there, but I’m missing it.

I have a little to-do base. Task records have two date fields: Do_On and Do_By. A task can have values in one or both or neither of the fields. I’d like to look at the stuff on a calendar so I need a single date field I can map to the calendar. Do_On takes higher priority than Do_By, so in plain English, I’d like a calc that looks something like this:

If both Do_On and Do_By are empty, then return Blank()
If Do_On is NOT empty, return Do_On
If Do_By is NOT empty, return Do_By

I’ve tried various things but can’t make it work. For example I’m told this is invalid:

If ( Do_On != Blank() ; Do_On ; Blank () )

Of course that’s not all I want but I thought I’d try just that much, but still, no luck. Can’t tell if I’m supposed to format the date result and I’m doing that wrong or if it’s something else. This doesn’t work either:

If ( Do_On != Blank() ; DateTime_Format ( Do_On ; "MM/DD/YYYY" ) ; Blank () )

Be grateful if someone could throw me a clue.

Will

[WilliamPorter]

I’ve made some progress. At least this formula is accepted:

IF(Do_On!=BLANK(), DATETIME_FORMAT(Do_On, 'M/D/YYYY'), BLANK() )

And it does return the value in the Do_On field if that field isn’t empty. However, for all other fields, it returns “#ERROR”. Have no idea why.

On the other hand, this formula seems to work:

IF(Do_On=BLANK(), BLANK(), DATETIME_FORMAT(Do_On, 'M/D/YYYY') )

Why does this fail when I test first to see if Do_On is NOT empty, but succeeds if I test first to see if Do_On is empty?

[WilliamPorter · Accepted solution]

By George, I think I’ve got it!

IF(AND(Do_On=BLANK(),Do_By=BLANK()),BLANK(),IF(Do_By=BLANK(),DATETIME_FORMAT(Do_On, 'M/D/YYYY'), DATETIME_FORMAT(Do_By, 'M/D/YYYY')))

I’m not sure but it looks like Airtable’s formula parser is not happy if I use spaces or returns to make a formula more intelligible. Would be nice also if the editor area was bigger.

Anyway, solved!

[AlliAlosa]

Hi there,

Try this instead:

IF({Do_On}, DATETIME_FORMAT({Do_On}, 'MM/DD/YYYY'), IF({Do_By}, DATETIME_FORMAT({Do_By}, 'MM/DD/YYYY')))

Airtable sometimes has trouble with BLANK(). When checking to see if a field is blank, it’s better to drop the first conditional statement and write the formula as it is above. “IF({Do_On}” is the same thing as writing “IF({Do_On} != BLANK()”

[WilliamPorter]

Thanks, @AlliAlosa! Your reply came in just as I was posting my last post saying I had figured out at least one way to do this. But your tip is very helpful and I’ll remember that!

Also, I figured out when to use curly brackets. Looks like “Do_On” counts as a one-word field name and so does NOT need to be put in braces.

Live and learn.

[WilliamPorter]

Well, thought I was done but just realized I have a follow-up question.

The point of this calculation was to generate a date value that I could map on a Calendar view. But apparently this formula field does NOT count as a date field as far as Airtable’s calendar is concerned. Not sure why.

Is there a way to coerce the data type of the result of this calculation to date? Or is there some other way to get a calculated date to serve as the basis of assigning records to a calendar view?

[AlliAlosa]

You’re welcome! And I noticed that - we posted at pretty much exactly the same time, haha.

Also, you’re right about Airtable not liking extra spaces and returns. I’m trying to find the post I read about it in… but am having no luck at the moment.

I like to use a text editor when working on complex formulas to match up parentheses and what not. If you copy and paste from the text editor into Airtable, Airtable usually accepts the line breaks and white spaces. My favorite right now is called Brackets. :slightly_smiling_face:

Good luck!

[AlliAlosa]

Oh! You’ll want to wrap the DATETIME_FORMAT() functions with DATETIME_PARSE() :slightly_smiling_face:

IF({Do_On}, DATETIME_PARSE(DATETIME_FORMAT({Do_On}, 'MM/DD/YYYY')), IF({Do_By}, DATETIME_PARSE(DATETIME_FORMAT({Do_By}, 'MM/DD/YYYY'))))

[WilliamPorter]

AlliAlosa:

IF({Do_On}, DATETIME_PARSE(DATETIME_FORMAT({Do_On}, ‘MM/DD/YYYY’)), IF({Do_By}, DATETIME_PARSE(DATETIME_FORMAT({Do_By}, ‘MM/DD/YYYY’))))

EXCELLENT!!The Airtable calc editor is, um, a bit more complicated than I would like it to be, but if I can get the job done, that’s what matters. And I now have a nice little calendar view that shows me what I’m supposed to be doing on certain days.

Oh, and thanks for the tip about Brackets. In my Mac OS days, there was (and is) really only one text editor to consider: BBEdit. But now that I work mainly in Windows I’ve found there are lots of text editors to pick. I’ve been using Sublime Text and Atom but will give Brackets a look.

Thanks so much again.

Will

[WilliamPorter]

Addendum for benefit of any other novices (like me) who might read this thread. After I got the calc working correctly (with Neads_Admin’s generous help) I set up my calendar view. When I did I was told that I won’t be able to drag tasks from one date to another because the field the calendar is referring to is a calc field. THIS MAKES COMPLETE SENSE TO ME and is a limitation I’m happy to live with!

However it might cause me later to rethink the way I’ve done this. It might make more sense for me to have a single date field and perhaps a second field where I identify the date as either a “do on” date or a “do by” date. There would be a downside to doing that. Having two date fields means I can indicate that a task must be done by next Friday but I want to work on it today. But on the plus side, I would be able to drag tasks on the calendar. Have to give that some thought.

Anyway, I should go to bed now, having learned enough for one day. :slightly_smiling_face: