Databinding & Formula

Posted by: mariva on 8 September 2017, 3:19 pm EST

  • Posted 8 September 2017, 3:19 pm EST

    Hello!
    I fill the sheet by setting values in the data model.
    The last column should contain the difference between NOW() and the previous column values. So I set formula for the last column:
     fpSpread1.ActiveSheet.Columns[10].Formula = "NOW()-$J1";
    .
    But it doesn't work.
    What am I doing wrong?
    2015/08/test_formuladatabind.zip
  • Replied 8 September 2017, 3:19 pm EST

    Hello,

    Thanks for the detailed explanation.
    Please note that there is no direct function to calculate the difference in between the datetime in the desired format(hh:mm).

    However, you can use following customized formula which calculates the Time difference(in hh:mm format) between the provided date and NOW().

    IF((MINUTE($B1)-MINUTE($A1))<0,(DATEDIF($B1,$A1,"D")*24+(HOUR($B1)-HOUR($A1))-1),(DATEDIF($B1,$A1,"D")*24+(HOUR($B1)-HOUR($A1))))&":"&IF((MINUTE($B1)-MINUTE($A1))<0,( (MINUTE($B1)-MINUTE($A1))+60), (MINUTE($B1)-MINUTE($A1)))

    In Simple Words it is:
    IF(Difference Between Minutes is Negative,((DayDifference*24)+ (HourDifference) - 1)), ((DayDifference*24)+(HourDifference)) : IF(Difference Between Minutes is Negative,(MinuteDifference+60),( MinuteDifference)

    Please refer to the attached modified sample/video implementing the same.
    Hope it helps.

    Thanks,
    Reeva

    2015/09/test_formuladatabind2_Modified.zip

    2015/09/ExcelFormula.gif
  • Replied 8 September 2017, 3:19 pm EST

    Hello,

    Apologies for the delayed response first of all.Thanks for providing the sample project.

    Please note in Spread, the Difference between dates are not calculated directly. For this purpose, specific DateTime functions like ‘DAYS360’ is provided. Please refer to following documentation links for more information on the same:-

    http://sphelp.grapecity.com/WebHelp/SpreadNet8/FR/formulas-functsdatetime.html
    http://sphelp.grapecity.com/WebHelp/SpreadNet8/FR/FunctionDAYS360.html

    Hence, in order to show the difference between current time and values in second last column, following formula needs to be set:-

    fpSpread1.ActiveSheet.Columns[10].Formula = “DAYS360(NOW(),$J1)″;

    Please refer to the modified sample implementing the same.

    Hope it helps. Let us know if you face any other issue.

    Thanks,
    Reeva
    2015/09/test_formuladatabind_Modified.zip
  • Replied 8 September 2017, 3:19 pm EST

    Hello, Reeva!
    Thank you for your example.
    But I need to see the difference between NOW() and some datetime value in HOURS and MINUTES.
    The format should be "h:mm".
    Is it possible?

    In Excel I can use (dt1-dt2) and it shows the difference in days like 67.583961. Then I can calculate hours and minutes.
    So the formula for the datetime difference in Excel is =INT((A1-B1)*24)&":"&TEXT(((A1-B1)*24-INT((A1-B1)*24))*60,"00").
    I need the same working in fpSpread. How can I do this????

    And also I've tried to use DAYS360, and it returns the number of days between two dates based on a 360-day year, so the result is different. Also I've tried to use “DAYS″ instead of "DAYS360" and it doesn't work. You can see these issues in a new attached example. And also the excel screen short is attached to see what I need to do in fpSpread.

    Thank you
    2015/09/test_formuladatabind2.zip


  • Replied 8 September 2017, 3:19 pm EST

    Thank you for your example!

    It works but the time difference is not correct. The reason is in DATEDIF. It rounds the difference, so if the real difference is 27.59997685 days for example, it shows 28.

    You can see the result in the attached project.

    How can I change the formula to show correct time difference?

    Thanx

    2015/09/test_formuladatabind3.zip
  • Marked as Answer

    Replied 8 September 2017, 3:19 pm EST

    Hi,

    Thanks for the sample code. We are sorry to mention but we are unable to replicate the ‘rounding off the difference’ behavior of ‘DATEDIF’ function. Please refer to the attached video(DateDif Behaviour.gif) which clearly shows that ‘DATEDIF’ function returns the absolute difference of days between the two dates(without rounding it off) just like that in Microsoft Excel. Please correct me if I missed something in order to replicate the problem.

    However, a comparatively simple and correct formula to get the difference between the dates in ‘hh:mm’ format is:
    IF(DATEDIF($B1,$A1,"D")>0,DATEDIF($B1,$A1,"D"),DATEDIF($A1,$B1,"D"))*24 +HOUR(DATEVALUE($A1)-DATEVALUE($B1)) & ":" & IF(SECOND(DATEVALUE($A1)-DATEVALUE($B1))<30,MINUTE(DATEVALUE($A1)-DATEVALUE($B1)),MINUTE(DATEVALUE($A1)-DATEVALUE($B1))+1)

    In simple words,
    (Difference Between Days *24 + Difference between Hours) : Difference between Minutes

    This formula uses DATEVALUE function which returns the DateTime object of Specified Date. Subtraction between two DateTime objects returns ‘TimeSpan’ in format of ‘dd hh:mm’. Here, the number of days are converted into hours(using DATEDIF) whereas Hours and Minutes are used as it is.

    Please refer to the attached modified sample(test_formula&databind3_Modified.zip) and Video(Correct Output.gif) implementing the same.

    Hope it helps.

    Thanks,
    Reeva

    2015/09/Correct-Output.gif

    2015/09/test_formuladatabind3_Modified.zip

    2015/09/DateDif-Behaviour.gif.zip
  • Replied 8 September 2017, 3:19 pm EST

    Thank you a lot, Reeva!!! That formula works!! =)
  • Replied 8 September 2017, 3:19 pm EST

    Hello,

    We are glad to know that your issue is resolved.

    Thanks,
    Reeva
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels