I recently built a “Wellness Log” base in Airtable that tracks all kinds of stuff: When I get up and go to be every day, when I eat each meal, how much H2O I drink every day, how long between each meal, and star-based ratings of my mood, mental focus, and energy/stamina.
What I have no idea how to do but really WANT to do is build formulas that calculate change over time using entries from other days. Specifically, I want to calculate the amount of sleep I’m getting every night, as well as net weight loss/gain since I started tracking my weight in the base.
Regarding the nightly sleep calculation, I have a “RISE” and “BED” field in each record that is a Date field type that includes the time in 12 hour format. I want to create a formula to identifies the preceding record and calculates hours of sleep based on the amount of time between “BED” yesterday and “RISE” today.
With the weight loss/gain I would need to ID the first record that has a value in the numeric weight field, and then find the difference between today’s weight and that number.
Formulas for the same record are really easy, and I’ve been able to do some really cool stuff with them. I have no idea how to perform calculations that require data in a DIFFERENT record, however, but I would think it has to be possible.
Am I missing something here, or do I just need some help with how to do it?
I'm running into a similar issue trying to track my sleep/wake habits using IFTTT button presses to create a new record for sleep and a new record for wake but similarly want to tie them together into one record. Alternatively, I've been looking for something that can create a single record for each sleep instance as a row instead but haven't found a good solution for that yet...