Forum Discussion

AngelaAngela1's avatar
AngelaAngela1
Qrew Trainee
12 years ago

calculating duration in days

Hello -

I am trying to calculate duration in days, which I have found the formula easily. However the math does not seem to be working correctly. For example, my team worked from 3/12-3/14...THREE full days from the morning of the 12th, all day the 13th then to close of business on the 14th. However when I do the equation: ToTimestamp([Reporting End Date])-ToTimestamp([Start Date]) it only calculates 2 days (14-12). Is there a more accurate way to calculate duration in days?


Formulas and functions
Tables and fields
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    12 years ago
    Dear Angela,

    You could try adding 1 to the answer, as the calculation is simply asking for the difference between the two but isn't counting one of the days.


    (ToTimestamp([Reporting End Date])-ToTimestamp([Start date]))+Days(1)


    That should give you the result including the start day.

    So if your project/job was 1 day long 3/12-3/12 your original calculation would return 0, the above would return 1.

    Jack
  • AngelaAngela1's avatar
    AngelaAngela1
    Qrew Trainee
    12 years ago
    Thank you!  That worked perfectly.  I tried to add the plus 1 before, however I did not have the "Days" in there so it was not working. Thank you for your help.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    This didn't work for me.  Failed due to an argument in the function.  Can you help?  Below is what I tried.  What did I do wrong?  The two date fields referenced are named as such.  Data type=Date/Time.

    The function ToTimestamp can be used with the following arguments:

        ToTimestamp (Date d).
        ToTimestamp (Date d, TimeOfDay t).


    (ToTimestamp([First Attempt Date/Time])-ToTimestamp([Date/Time Escalated]))+Days(1)
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    Dear Anthony,

    What is the field type that you are doing the calculation in?

    Jack
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    Field types are date/time but the field with the formula is a 'duration' type field.  Sorry for missing that.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    Using a formula duration field try:

    (ToTimestamp(ToDate([First Attempt Date/Time]))-ToTimestamp(ToDate([Date/Time Escalated])))+Days(1)

    This will give you a result in days.

    From the error I assume this only works with either date fields (not date and time) or date and time of day fields. To get this to work with your date and time fields I have added the ToDate conversion which removes the time element from the calculation only returning the date.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    I think this worked, Jack!  Thanks a lot!!

    The calculation for the duration from say, 9/17 (12:00AM) to 9/18 (12:00AM) counts as 2 days.  I will look into this part some more, maybe I'll hit you up again if you don't mind. You got me farther than I was before.  Thanks again!
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    No problem at all Anthony. If you want to solve the 2 day issue try this ToTimestamp(ToDate([First Attempt Date/Time]))-ToTimestamp(ToDate([Date/Time Escalated]))

    The calculation I provided for Angela above was for a slightly different use, she needed an additional day, by taking the +1 days off the end and the related ()'s this should give you 1 day.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    LOL...yes, it dawned on me later I had padded an extra day.  I did just as you said and it fixed it right up!  Thanks again, Jack.  Have a great day!