Skip to main content

Hello!

I’m an item reseller who has been experimenting with DATETIME_DIFF to show me how long an inventory item was listed for before it sold, but I’ve been getting unexpected results. It’s possible I’m doing something very wrong, so if you could please take a look, I’d appreciate it.

Basically, I have a “Master” table which tracks all transactions by date. I then have an “Inventory” table which tracks my items, and their current status and info. Inventory has a “Date Listed” column which is a normal date. Master has a “Transaction Date” column which is also a normal date. These tables are linked via a “Sold On” column in Inventory, which pulls in the date that particular item was sold, from Master. So far so good.

When I try and create a Formula column in Inventory which calculates the difference between Date Listed (native column) and Sold On (linked), it doesn’t calculate properly. Here is my formula:

DATETIME_DIFF({Sold On},{Date Listed},'days')

It seems simple, but something is going wrong. For example, I have an item with a Date Listed of 4/2/2020 (Feb 2), and a Sold On of 10/2/2020 (Feb 10), which should calculate as 8 days. But it calculates as 241. It’s like it’s treating the Sold On (linked) date as October 2, rather than Feb 10, but I don’t see a way of checking that. Both display properly.

Any ideas? Thank you!

Are both date fields of the same format, timezone etc?

Could also be a date unit order issue. I’m used to the first number being the month rather than the day - so I would read 10/2/2020 as October 2 rather than February 10.

Hi there, thanks for your quick reply.
They are the exact same format - I use the calendar picker rather than manually typing in a date, so when I pick February 11, 2020 from the calendar, it shows it to me as 11/2/2020, which is the format I’ve chosen for the datetime representation (“Local”).

I agree visually the format can be interpreted different ways, but the backend which is doing the DIFF should be converting both formats into something standard (i.e. Unix milliseconds, maybe) to do the calculation, so it shouldn’t matter which format is used…

Hi there, thanks for your quick reply.
They are the exact same format - I use the calendar picker rather than manually typing in a date, so when I pick February 11, 2020 from the calendar, it shows it to me as 11/2/2020, which is the format I’ve chosen for the datetime representation (“Local”).

I agree visually the format can be interpreted different ways, but the backend which is doing the DIFF should be converting both formats into something standard (i.e. Unix milliseconds, maybe) to do the calculation, so it shouldn’t matter which format is used…


Odd. I would think the same. I was able to link up things properly in a test base without issues.

Same formula:

DATETIME_DIFF({Sold On},{Date Listed},“days”)

I did notice some odd results with the lookup (Table 1, Sold On) depending on the formatting settings I chose on the european format. Here are the settings that worked well:

Date Listed

Sold On (Table 2)

Sold On (Table 1 Lookup)

Odd. I would think the same. I was able to link up things properly in a test base without issues.

Same formula:

DATETIME_DIFF({Sold On},{Date Listed},“days”)

I did notice some odd results with the lookup (Table 1, Sold On) depending on the formatting settings I chose on the european format. Here are the settings that worked well:

Date Listed

Sold On (Table 2)

Sold On (Table 1 Lookup)


Hmm, yes, it works with the separate link and then the lookup. I wonder if there was some other underlying issue with my table setup, or if this is a bug… This is a good enough workaround for now; I think I’ll have to pull everything apart in the future and get under the hood a little more thoroughly later - basically what doesn’t seem to work is if one of the columns in the DATETIME_DIFF is a linked date column (but not a lookup). I also switched all of my date formats to European, as Local seems to be relative, but that didn’t resolve the issue by itself. Weird. Will do more tinkering and see what happens.

Thanks for your workaround!

Terms & ConditionsAccessibility statement