Date formula with Timeline Block?


#1

Hi All

I am in early stages of learning formula functions, and focusing on date based formulas.

One thing I have noticed is that dates created from formula calculations to not seem to work with the Timeline block.

Has anyone found a way to make this work?
Very important for me and hoping there is a solution.

Thanks, Rob


#2

It’s working for me. The formula gives you a date? Check the Formatting options in the field configuration.


#3

Thanks for the response.

Can you provide a copy of the formula you use that works?


#4

I did just Date :smile:, I mean, just the value from the Date field.


#5

Hey Elias

We may be talking about two different things - I am using a formula field to create a date output (such as DATEADD), which I am then trying to use as a reference point in the Timeline setting for ‘Start date field’ and ‘End date field’.

Sounds like you are just using a Date type fields. That works fine for me, but was not the query.


#6

Hi,

Not sure what is not working for you. I made my check and it worked for me.
I put two date fields with a formula using DATEADD, and then created a timeline block.
I share my results to you.

Hope it can give you a clue of what is not working.

Rgds


#9

Me too.

No, I think I said it right:

And I told my Formula: just the value from the Date field, in the second Formula field, of course. You weren’t specific about any function. :man_shrugging:

Also, you didn’t reply to my first question. I think the first step is to confirm that you get a date in Formula. :ok_hand:


#10

Hi Luiggi

Thanks for so much for your response, it was very helpful. I have copied what you have done and have screen shots below.

Some queries and items that I are still not working on my end

  • You did not show your formula in the {today} field, assumed is was TODAY()…is that correct? (I have screen shot of mine below)

  • My dates output using your formulas show up as dates and times, with the dates being MM/DD/YYYY when I want then to be DD/MM/YYYY (and no times). How come yours show the correct date format and not time?

  • To the above point, when I try to fix this using a DATETIME_FORMAT formula it fixes the date display fine, but then the Timeline Block does not work. It gives an error of ‘End date field does not contain dates’. (see the last screen shot). Does the Timeline Block not work when the DATETIME_FORMAT formula is used?

Sorry for all the questions, just keen to fix this issue and you have been so helpful already.

To give you the full picture, I am trying to build a software forecast document so that our R&D team can provide a central location to inform the company of expected dates of software feature releases upcoming. The engineers will normally put in the quarter (for example ‘Q3-2018’, being July-Sept 2018). I then want to Airtable to use an ‘IF’ formula to read the quarter entered and extract that into a start and finish date column, and then use the Timeline block to map the Q3-2018 range visually.

Guess what I am saying is that the formula is only going to get more complicated after I figure out the initial query.


#13


#14


#15


#16

And there is your problem,right there: you are using DATETIME_FORMAT which, as specified in its description:

Formats a datetime into a specified string.

(emphasis mine). The timeline block needs dates as input data, not date-ish strings.


#17

Hi,

Glad to help.
Regarding your questions:

  1. Yes, I use today as the formula for “today” field.
  2. I think what you are triying to achieve with your date formatting is easy to solve with the formating mini tab you have when you create a formula. In that tab you can eliminate the time option and you have some format options for the dates. (I include screenshot)

About the full picture, I will try to help you in my spare time to see if I can give you some tip about that. I’m a little bit short of time this week end.

Hope to be helpful.

Rgds


#18

Thanks Martin
I am a construction guy who moved into software a year ago, so there is a steep learning curve!


#19

I’m sorry but I have to say it.

That was my first reply :man_facepalming:


#20

No problem; I know how ist is. What computers and what humans think is a data type like a date ist fundamentally different, so it is easy to mix up both, then get bitten by that. For human readable presentation of the underlying data type in Airtable (as long as it not a string), there is the second tab in the field options popup; explicit conversion to another type should be done only when you are fully aware what you are doing and why it is necessary (numeric formula fields have odd issues with grouping / sorting, for instance, so shadowing them with a string converted version is an ugly but necessary hack).

One last word of caution: be wary of conversions in formulas, not all are explicit (like your DATETIME_FORMAT is); if you compare a non-string field with a string (e.g. if you do IF({my numeric field} = "", A, B), for instance, Airtable will implicitly convert all resulting values into strings (the right way to do this ist to check for non-emptiness with IF({my numeric field}, B, A), in case you are wondering).


#21

Be kind. Nobody imbibed programming paradigms (like type systems) from birth; we all learned that stuff at some point, no harm in doing it here.


#22

I don’t want users to know, I want them to read what we reply and do what we advice.

If they don’t do that (and missuse our time -I felt stupid giving the correct path and being ignored-), I think I can al least, point it.

Oh, and of course is nothing personal with @Robert_Hudman, I spent a lot of time in the forum and I just feel disappointed when someone does not follow the questions, steps, links, etc.


#23

Hey Luiggi
Well that makes a lot more sense…thank you!
Any help you can offer would be awesome.


#24

Hi Elias
I don’t know if I have somehow not followed your instructions(?), but can assure you I worked through them in detail before coming back to ask follow up questions.
Your advice is great appreciated.
Robert