Hi. I’m looking for a way to calculate an average time of day for an average start time and average end time. Airtable has the date and time field, and the time duration field, but
This is to get an average time a person fell asleep (potentially before 11:59 pm or after 12:00 am) and their average wake up time. Based on my pretty thorough knowledge of Airtable, I can’t figure out a way to do this because Airtable’s fields don’t offer you a time of day without a specific date.
A more traditional business purpose of this calculation would be calculating a person’s average clock in time and clock out time, as separate values, but still accommodating for before or after midnight, which could be applicable for a business with night/3rd shift workers.
It's my understanding that there is no technical workaround and that this simply a limitation of the types of data fields available. If you know of a workaround, I’d love to learn of it.
If you're comfortable setting a boundary of time you could just try adding 12 hours to it to get around the midnight issue? This would mean that it wouldn't work for times added at 11:55 am etc though, as we're basically just shifting that problem, you know what I mean
Here's an example:
I basically took the value from the date field and converted them to all be on the same day and added 12 hours to it. I then converted it to a UNIX timestamp, averaged that value, converted that back into a date and deducted 12 hours from it. I know how weird this sounds, but I....think it works?
If you can get @Dan_Montoya's suggestion working that'd be the simplest of course; I couldn't figure out how to make it work (I must have messed up the setup somewhere) and ended up going down this very weird rabbit hole heh