Skip to main content
Solved

Return Days and Hours from 2 Date/Time Fields?

D

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.



Any and all help would be appreciated!

Best answer by Dakota_Bushnell

Justin_Barrett wrote:

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.


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:


View original
    Did this topic help you find an answer to your question?

    7 replies

    D

    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.



    Any suggestions?

    J
    Dakota_Bushnell wrote:

    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.



    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.

    D
    Justin_Barrett wrote:

    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?

    D
    Justin_Barrett wrote:

    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.


    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.

    D
    Justin_Barrett wrote:

    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.


    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:


    K
    J
    2 people like this
    • K
      Kendy511
    • J
      Justin_Barrett
    J

    Awesome! Glad to see that you solved it. :thumbs_up:

    D
    1 person likes this
    • D
      Dakota_Bushnell
    C
    • Christiaan_Huiz
    • Participating Frequently
    • 16 replies
    • November 18, 2019
    Justin_Barrett wrote:

    Awesome! Glad to see that you solved it. :thumbs_up:


    This is a great contribution and it helped me out, however not directly. I did copy - paste while I had created everything the same to test the code and I am sorry to say, but it did not work for me. I went back to my own base and started building the formula from scratch and after a few mistakes, its works for me. The logic is the same, the wording is not (I used French). I hope this helps a fellow user. I am not sure what explains the difference. Please note, I did not use the IF statement.


    DATETIME_DIFF({Retour à Bury}, {Début de voyage}, ‘Days’) & " Jours, "

    & (DATETIME_DIFF({Retour à Bury}, {Début de voyage}, ‘hours’) - (DATETIME_DIFF({Retour à Bury}, {Début de voyage}, ‘days’)*24)) & " Heures et "

    & (DATETIME_DIFF({Retour à Bury}, {Début de voyage}, ‘minutes’) - (DATETIME_DIFF({Retour à Bury}, {Début de voyage}, ‘hours’)*60)) & " Min"

    J
    1 person likes this
    • J
      Justin_Barrett

    Reply


    Most helpful members this week

    Terms & Conditions