Return Days and Hours from 2 Date/Time Fields?

Hello fellow Airtablers.

I’m looking for something pretty specific, but not sure if it’s even possible.

I have a “Created Time” field and a “Completed Date/Time” field. What I’m looking to create is a field with a formula that will generate both the # of work days and # of hours between the two. Is this even possible, currently?

Currently the formula I have that only provides # of work days is:

IF({Completed Date/Time}, WORKDAY_DIFF({Created Time}, {Completed Date/Time}))

This is what it looks like.

Days%20%26%20Hours%20Spent%20Example

Any and all help would be appreciated!

I’ve compiled some other things I’ve found on the forums and come up with this

IF(
		{Completed Date/Time},
		 DATETIME_DIFF({Completed Date/Time}, {Created Time}, 'days') & " Days " & 
		 MOD( DATETIME_DIFF({Completed Date/Time}, {Created Time}, 'hours'), 24) & " Hours ")

This works for the most part, however I feel like the hours aren’t exactly correct.

Days%20%26%20Hours%20Spent%20Ex%202

Any suggestions?

You may need to fiddle with timezone options. Your date field is probably assuming it’s your local timezone, while the formula field is assuming something else (often GMT). You can adjust the formatting of all date fields (explicit and formula-driven) to be the same, or you can use the SET_TIMEZONE function to put the formula in line, or some combination of both.

Justin,

The {Completed Date/Time} and {Created Time} fields are both accurate. The discrepancy is only within the {Days/Hours Spent} fromula field. I’ve noticed while looking through my very large list that some are 100% accurate while others are an hour or so off. I determined that when I’m putting in Days and Hours, and possibly even minutes, it’s using the full amount of hours instead of just the remaining hours after how many days. I’m not sure if there’s a way to even do that? I might be limited to doing either only hours or days to at least be as accurate as possible. Not sure if there’s a way to convert hours into days/hours. Thoughts?

I’ve even attempted to run it like this:

Where {Hours Spent} Formula is:

IF(
{Completed Date/Time},
DATETIME_DIFF({Completed Date/Time}, {Created Time}, ‘hours’))

and {Days Spent} (which will be changed to {Days/Hours Spent} if I can get this to work) Formula is:

IF({Completed Date/Time}, INT({Hours Spent}/24) & " Days " & MOD({Hours Spent}/24,1)*24 & " Hours")

I wish Airtable had the “Convert” function that Excel does, or something similar.

It appears that maybe the conversion to hours itself isn’t the most accurate? Still running into issues where some of them are accurate and other are about an hour or 2 off.

I think a colleague and I may have determined the reason behind the “discrepancies” .

We weren’t taking daylight savings into account, as where Airtable automatically calculated it properly. That would explain why some are off by an hour and others are not.

Can’t tell you how foolish I feel for not even thinking of that until now.

Current Formula that does what we want:

IF({Complete Checkbox} = 1, DATETIME_DIFF({Completed Date/Time},{Created Time},‘days’) & " Days, " & (DATETIME_DIFF({Completed Date/Time},{Created Time},‘hours’)-(DATETIME_DIFF({Completed Date/Time},{Created Time},‘days’)*24)) & " Hours, " & (DATETIME_DIFF({Completed Date/Time},{Created Time},‘minutes’)-(DATETIME_DIFF({Completed Date/Time},{Created Time},‘hours’)*60)) & " Minutes")

Which looks something like this:

1 Like

Awesome! Glad to see that you solved it. :+1:

1 Like