Skip to main content
Question

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?


This topic has been closed for replies.

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
    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.
    Your most welcome and I'm really pleased this resolved your issue.
    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)
    Dear Anthony,

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

    Jack
    Field types are date/time but the field with the formula is a 'duration' type field.  Sorry for missing that.
    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.
    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!
    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.
    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!
    Terms & ConditionsAccessibility statement