Duration field (measuring days) missing a day

[Emma_MH]

Hi everyone,

So I’m trying to figure out, on average, how long it takes me to close a sale. So I have the creation date for a record and the closing date for a record, but when duration calculations are missing a day (ie if I started a sale on 3/2 and finished it on 3/12 it’s saying 9 days. And even weirder is if, say, I close a sale the day after I created a record, even though one day has passed and the dates in the fields reflect that, it says “0”).

I’m using the formula DATETIME_DIFF({End Date}, {Start Date}, ‘days’) and have tried both integer and decimal formats. The duration format doesn’t work when you’re working in units of days.

I don’t want to just add “+1” to the formula because if I close a sale the same day I want that to say 0. Presumably it’s not counting the start or end date rather than subtracting the number of days from the end to the start date but why?

Anyone know how to fix this?

[W_Vann_Hall]

Typically when you find any date calculation off by a day, it’s because one or more of your fields are set to use GMT and the others aren’t. Even when you aren’t displaying a time field, Airtable keeps track of this — and, sometimes, the setting seems to get ‘sticky.’

To check this, open the ‘customize field type’ menu for each of your date fields, toggle ‘Include a time field’ if it isn’t already, and either select or deselect ‘Use the same time zone (GMT) for all collaborators.’ Deselect ‘Include a time field’ if appropriate, and save your changes.

If you find all fields are set to either use or not to use GMT, you may want to try toggling the GMT field on and off (or off and on, depending). There have been reports of this toggle getting ‘stuck’ and the field not reflecting the setting.

If that doesn’t fix your problem, come back and we’ll try again…

[Emma_MH]

So that was something I had checked before having looked in this forum for a solution previously and both were off. I tried just now to turn both of them on and that didn’t fix it. Then I tried turning them off again and that didn’t work either. You could be on to something though because when I turned the GMT time on and then turned “include a time field” off, one of the date fields kept the GMT toggled and one did not. But I tried toggling both on and off in different combinations a bunch of times and that still didn’t fix the problem.

[W_Vann_Hall]

Oh, duh — sorry, brain freeze.

Durations assume units of seconds. Use this formula

DATETIME_DIFF({End Date}, {Start Date}, 'seconds') 

and then format as duration. When I set {End Date} and {Start Date} one day apart, I get

24:00:00 

when I configure the response as a duration with format of hh:mm:ss.

Sorry about missing that the first time by…

---

On second thought, I’m not exactly sure what you’re doing; the reference to ‘durations’ seems contradicted by the screenshot you posted. In any case, my integer- and decimal-formatted formula results work fine — one day = 1 day, two days = 2 days, and so on, and my duration-formatted version works fine, as well.

Are you running a native executable (e.g., the Windows or macOS Airtable app, or a mobile client) or using a browser-based interface?

[Emma_MH]

I am using a browser-based interface (Chrome).

From what further I’ve deduced, the problem is that when I create a closing date without a time, it makes the time midnight while the creation date takes the time into account even if I don’t have “include a time field” checked. if I closed a sale today and mark that as the date but created the record this morning, it’s giving me negative hours. And if I create a record at, say 3pm on 3/8 and mark it as complete on 3/9 and it’s giving it a time of midnight, then it’s saying it’s 9 hours (which is less than a day when I convert it to days).

Is there a way to get it to autopopulate the created date but ignore the created time and just count days total? Their support section makes it seem like this should work fine.

[W_Vann_Hall]

Ah, hadn’t caught that you were using CREATED_TIME() for your {Start Date}.

To set a date based on CREATED_TIME() to midnight, use this:

DATETIME_PARSE(
    DATETIME_FORMAT(
        CREATED_TIME(),
        'MM-DD-YYYY'
        ),
    'MM-DD-YYYY'
    )

The format of the, um, format string is unimportant, as long as it doesn’t include a timestamp. That way, when you convert it to a string and back into a time field, Airtable assumes a midnight timestamp.