Skip to main content

calculating a date difference in a rollup field

B
Forum|alt.badge.img+3

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

Mike_AutomaticN
Forum|alt.badge.img+21

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

 

B
Forum|alt.badge.img+3
  • becca826
  • Author
  • New Participant
  • 1 reply
  • January 30, 2025

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.

TheTimeSavingCo
Forum|alt.badge.img+28
becca826 wrote:

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'

 

Adam, Consultant @ thetimesaving.company
ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8805 replies
  • January 30, 2025

@Becca13 

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

Reply


Most helpful members this week

Terms & Conditions