Help

Re: Formula for working out units per hour?

4423 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Martin_Hill
4 - Data Explorer
4 - Data Explorer

Hi,
I’m trying to work out how to get units picked per hour?
In Excel I do the following…
convert duration time from h:mm to integer by *1440 with the cell formatted to a number then I use SUM({units picked}/{time in minutes}*60)
and this gives me units picked per hour.
Just wondering if anyone knows how to do this type of formula in Airtable?

Many thanks,
Marty

2 Replies 2

It sounds like there are three steps to your issue:

  1. Getting the duration in hours.
  2. Getting the total units picked.
  3. Dividing units picked by duration.

1. Getting the duration in hours

If you have a Duration field type, it stores the time internally in seconds. To convert seconds to hours, divide by 3600.
{Duration} / 3600

If you have two Date fields (which can also include times), you can use the DATETIME_DIFF([date1], [date2], ‘units’) formula to get the duration time. Use 'h' as the units to get the duration in hours.
DATETIME_DIF({EndDateTime}, {StartDateTime}, 'h')

If you have duration as a number of days or minutes (1 day = 1440 minutes), you can convert to hours
{DurationAsNumDays} * 24 if your duration is in days
{DurationAsNumMinutes} / 60 if your duration is in minutes

If the duration is a Single line text, you will need to parse it into a numeric value, which can be done, but you are better off converting it to a different field type.

2. Getting the total units picked

If your total {units picked} is already in a single numeric field in the same table, you are good to go.

If your {units picked} are in linked records, you will need a rollup with the SUM(values) aggregation formula.

3. Dividing units picked by duration

Depending on how your data is setup, you may be able put everything in one formula. However, if you need to use a rollup, you will need multiple fields: one for the linked records, one for the rollup, and another for the formula.

If your duration is a Duration field:
{Units Picked} / ({Duration} / 3600)

If you have fields for start and end date/times:
{Units Picked} / DATETIME_DIF({EndDateTime}, {StartDateTime}, 'h')

If your duration is a numeric field:
{Units Picked} / ({DurationAsNumMinutes} / 60) or
{Units Picked} / ({DurationAsNumDays} * 24)

Things get a little trickier if you need to rollup times as well as units picked, but hopefully this gives you enough to work with.

Martin_Hill
4 - Data Explorer
4 - Data Explorer

Thank you for the fast reply, much appreciated!