Discussions

Expand all | Collapse all

Calculate the number of days between Today and Date Modified

Laura Thacker07-19-2018 14:24

  • 1.  Calculate the number of days between Today and Date Modified

    Posted 07-19-2018 14:21
    I need a formula to calculate from todays date to the time a project was last modified.
    Any help would be appreciated!!


  • 2.  RE: Calculate the number of days between Today and Date Modified

    Silver
    Contributor
    Posted 07-19-2018 14:24
    Today() - [Date Modified]


  • 3.  RE: Calculate the number of days between Today and Date Modified

    Posted 07-19-2018 14:26
    If you want a numeric field for the # of days , it would be
    ToDays(Today() - ToDate([Date Modified]))


  • 4.  RE: Calculate the number of days between Today and Date Modified

    Posted 07-19-2018 14:28
    Here is what I get:

    The expression Today() on the left hand side of the operator "-" is of type datewhile the expression [Date Modified] on the right hand side is of type date/time. The operator "-" cannot be used with these types of expressions.


  • 5.  RE: Calculate the number of days between Today and Date Modified

    Silver
    Contributor
    Posted 07-19-2018 14:34
    This is because the [Date Modified] is a Date/Time field, and Today() is a "Date" value. 

    Mark's comment about displaying the number of days as a numeric value would be the formula to use:

    ToDays(Today() - ToDate([Date Modified]))


  • 6.  RE: Calculate the number of days between Today and Date Modified

    Posted 07-19-2018 14:35
    Did you try my formula as a Formula numeric field type?


  • 7.  RE: Calculate the number of days between Today and Date Modified

    Posted 07-19-2018 17:13
    Worked like a charm!!!  Thank you kindly.


  • 8.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 15:57
    I'm trying to get the difference between todays date and the expected start date of a project but if the project has been started already, I want the value to be NA.  I have a text field and it's not letting me put the "-", if I do a numeric, it won't let me do the value NA

    If(
    not IsNull([Started]),"N/A",
    ToText(([Start])-(Now())
    )
    )


  • 9.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:15
    Try this

    If(
    not IsNull([Started]),"N/A",
    ToText(ToDays(Today() - [Start])))


  • 10.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:26
    I'm getting an error, "-" can't be applied to dates and times


  • 11.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:33
    Can you tell me what type of field [Start] is?


  • 12.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:36
    It is a date/time field, same thing with Started.  


  • 13.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:38
    Ok, try this

    If(
    not IsNull([Started]),"N/A",
    ToText(ToDays(Today() - ToDate([Start]))))


  • 14.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:49
    That worked! Thank you!  One last question, I want the value to be negative to indicate that the task has been done after the expected start date.  I tried to just reverse the 2 functions but now it doesn't work. 


  • 15.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 16:58
    Sure, I made the assumption that you wanted a positive number

    If(
    not IsNull([Started]),"N/A",
    ToText(ToDays(ToDate([Start]) - Today())))


  • 16.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 17:07
    Thank you so much!!!!!


  • 17.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 19:10
    Ultimate last question on this one.  Is there a way that I can have one decimal place on the number value on this field?


  • 18.  RE: Calculate the number of days between Today and Date Modified

    Posted 02-07-2019 19:36
    If(
    not IsNull([Started]),"N/A",
    ToText(ToDays(ToDate([Start]) - Today())))

    & ".0