Skip to main content

Calculating Average Time of Day

fouadabourizk

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

  • I need the average start time and end time, as separate values.
  • I need to accommodate for a start time being before midnight or after midnight.

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.

4 replies

Dan_Montoya
Forum|alt.badge.img+7
  • Dan_Montoya
  • Employee
  • 272 replies
  • February 4, 2024

You can use a formula to extract just the time.  Then use a roll up to another table. In the roll up use the average (values).   You can then use a lookup in table a to show the average.  

TheTimeSavingCo
Forum|alt.badge.img+18

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

If you can't set a boundary...probably you could do some crazy thing with JavaScript to handle it.  I was Googling trying to handle this issue and people were converting time to radians and stuff.  It was all over my head, but the output seemed to be correct, so that might be a path you could look into as well!

 
Adam, Consultant @ The Time Saving Co
fouadabourizk
  • fouadabourizk
  • Author
  • New Participant
  • 2 replies
  • February 5, 2024
Dan_Montoya wrote:

You can use a formula to extract just the time.  Then use a roll up to another table. In the roll up use the average (values).   You can then use a lookup in table a to show the average.  


Thank you. I appreciate your help.

fouadabourizk
  • fouadabourizk
  • Author
  • New Participant
  • 2 replies
  • February 5, 2024
TheTimeSavingCo wrote:

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

If you can't set a boundary...probably you could do some crazy thing with JavaScript to handle it.  I was Googling trying to handle this issue and people were converting time to radians and stuff.  It was all over my head, but the output seemed to be correct, so that might be a path you could look into as well!

 

Hi, that makes sense. I appreciate your thorough response. I’d rather not make another table for this. Thank you for your assistance. 

Reply


Most helpful members this week

Terms & Conditions