Help

Re: calculating a date difference in a rollup field

249 0
cancel
Showing results for 
Search instead for 
Did you mean: 
becca826
4 - Data Explorer
4 - Data Explorer

Hi all,

I'd like some help w/ a rollup field.

Table A is a summary record for an order and table B has the line items of an order.  Each line item can have a different delivery date.  I'd like to create a rollup field in table A that calculates the difference between the min and max delivery dates of all line items in table B.   

This rollup field presently looks like: DATETIME_DIFF(MAX(values),MIN(values),'days').  When I do this, I always get values of 0 returned.  I suspect that I cannot use a datetime_diff function within a rollup but I'm not getting an error so I wanted to confirm?  (I also just tried max(values) - min(values) but that doesn't work either, (also returns 0s).  I'd rather have a single field in my data to perform this function over 3 (one for min, one for max, one for the difference).  Any suggestions / confirmation that this just isn't possible using dates?

Thanks!

4 Replies 4

Hey @becca826,

I never tried this before, but it is an interesting use case. 

Did you try including the time unit for the date time diff? See suggested fórmula below:

DATETIME_DIFF(MAX(values),MIN(values), “days”)

Please let me know if that works! I’m on my phone rn, so I cannot give it a shot easily. 

Mike, Consultant @ Automatic Nation

 

becca826
4 - Data Explorer
4 - Data Explorer

ah yeah, thanks - that was just an unfortunate typo on my part in my question (now edited)- I did in fact write the formula using days..  It would throw a formulaic error without the days in there I think.  That's why I'm a bit perplexed.  I figured if this weren't a valid construction that it wouldn't let me actually use the formula.

Yeap, confirming.  Doing the following will just give you '0 test' as well, and DATETIME_FORMAT just errors out too

MAX(values) & ' test'

 

@Becca 

I think you just stumbled upon a bug there in Airtable, because that formula SHOULD work properly in a rollup field.

Please be sure to report that as a bug to support@airtable.com, so they can hopefully address it in the future!

- ScottWorld, Expert Airtable Consultant