New data not reflected in fields, bases

[Edd_Turner]

Phew! Lets’s see if I can explain this clearly.

Almost daily I go running.
I keep a log of distance and time and get a kick out of recording and graphing the results.
In Airtable, I’m using the chart app to chart several bits of data (DIstance, Time, s per km, s per mile, speed, etc.)

For the straight forward data (duration, time) Airtable has no problem making charts for me, they look great!

But, Airtable won’t let me make a chart with duration. No problem: the current workaround is as follows (example to calculate km/h):

1. Insert Distance as Decimal in “Distance” field
2. Formula -> convert to m (divide by 1000) (new field)
3. Insert Duration as Duration in “Time” field
4. Formula -> convert to s (divide by 60) (new field)
5. In “km/h” field, formula {distance}/{time}*3.6

This then gives speed and so far has been carefully charted every day without fail

But when I try to do something else (e.g. duration for fixed distance - mins per km) the fomula works for all previous entries but never the new ones!

Method:

1. Insert Distance as Decimal in “Distance” field (as above)
2. Formula -> convert to m (divide by 1000) (new field) (as above)
3. Min/km field - Formula ({Time}/{Distance}) (works fine)
   4. Km time - identical field as point 3, but with "number"
   5. Comes up blank, doesn’t chart

I hope that’s clear - what have I missed that the field doesn’t calculate (and therefore won’t appear on the chart?)

I’m hoping to solve this soon as if I need to rebuild the whole thing then it won’t be too big!

Thanks in advance

Edd

[Julian_E_Post]

Hi Edd,
Can you provide a screenshot of the base and the formula that’s not working?

[Edd_Turner]

Sure!

Firstly, here’s the base:

,

And here’s the first instance of numbers not converting (copying, as the field is a duplicate) from mins to sec…

Let me know if you need more!

[Julian_E_Post]

I think I see what’s happening - let me know if this sounds right to you. The “Number” field type does not support formulas. In other words, if you switch from “Formula” to “Number”, it will stop calculating, and become a static field. If your intention is to convert mm:ss to seconds, you can do that with a formula!

Is your min/Km field is formatted in mm:ss? If yes, make the sec/Km field a formula field. Then just put the {min/Km} field into your formula without any calculations. It should automatically show the units as seconds.

[Edd_Turner]

I think I’ll have to re-read that 3 or 4 times, but will give it a go and report back - thanks! :thumbs_up:

[Julian_E_Post · Accepted solution]

Simple answer: change the sec/Km field to a formula, and enter {min/Km} as the formula. See what happens!

[Edd_Turner]

Brillant - thanks!

I think I thought a chart couldn’t reference a formula - guess I was wrong!

Now if only I could make a chart showing minutes and seconds instead of just seconds - that yould be perfect!