Feb 21, 2020 06:09 AM
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
Feb 21, 2020 12:58 PM
It sounds like there are three steps to your issue:
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.
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.
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.
Feb 22, 2020 03:46 AM
Thank you for the fast reply, much appreciated!