using operators such as greater than or lower than to compare hours

Posted by: andreti-fp on 8 September 2017, 1:49 pm EST

  • Posted 8 September 2017, 1:49 pm EST

    <BLOCKQUOTE>

     Hello:


     I am requiring help for the instruction to compare hours.  For example i need to compare a hour contained into a cell with


     a standard hour defined in the formula.


     Thanks for your support.


    Andreti

    </BLOCKQUOTE>
  • Replied 8 September 2017, 1:49 pm EST

    Andreti,



    You can use the TIME function to enter a constant time.  Suppose you need to determine whether or not the time in cell A1 is before 9:00am.  If cell A1 contains a time value stored as a number between 0 and 1 (like in Excel) or stored as a TimeSpan object then you could use the following formula...


        fpSpread1.Sheets[0].Cells[0, 1].Formula = "A1<TIME(9,0,0)";


    If cell A1 contains a date/time value stored as a number greater than 1 or stored as a DateTime object then you could use the following formula which extracts the time part before performing the compare operation...


        fpSpread1.Sheets[0].Cells[0, 1].Formula = "TIME(HOUR(A1),MINUTE(A1),0)<TIME(9,0,0)";

  • Replied 8 September 2017, 1:49 pm EST

    Hello:


     thanks for your support provided. 


    My concern is related with the instruction indicated:


    Dim DT As DateTime = FpSpread1.ActiveSheet.Cells(0, 1).Value


            MsgBox(DT.Hour).


    I am not sure if this could help me. As i understood this is going to show a message box.


    what i really need to do is compare hours using if:  Example if value in cell (x;x) > than 9:00 am then :calculate formula. 


    I Would appreciate your help.  regards, Andreti

  • Replied 8 September 2017, 1:49 pm EST

    Hello,


    Did you read the post after that one in the thread from bobbyo? Using his instructions should get you the results you are looking.

  • Replied 8 September 2017, 1:49 pm EST

    Hello Andreti, 

    Are you working with DateTimeCellType? If yes, you may use the value property of the cell to get the value of the cell and convert it to DataTime. You may then use the standard DataTime functions to get the time difference.

    To get the hours you may use following code:

            Dim DT As DateTime = FpSpread1.ActiveSheet.Cells(0, 1).Value

            MsgBox(DT.Hour)

    Regards, 

  • Replied 8 September 2017, 1:49 pm EST

    thanks for your help.  It resulted me very well


     The other question i have. I am trying to represent 2:00 a.m into the function TIME(9,0,0), example instead of 9:00 am the function used was TIME(2,0,0) but it did not work.


    Does it use military hour? What would be the correct number for 2:00 a.m to be used.


     Thanks

  • Replied 8 September 2017, 1:49 pm EST

    Hello,

    Following are the parameters of the TIME function along with their description.

    hour Hour as a number from 0 to 23. 
    minutes Minutes as a number from 0 to 59. 
    seconds Seconds as a number from 0 to 59.  

    You may try the below snippet to get the desired result:

    Dim dt As New FarPoint.Win.Spread.CellType.DateTimeCellType

    dt.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.TimeOnly

    FpSpread1.ActiveSheet.Cells(1, 1).Formula = "TIME(2,0,0)

    FpSpread1.ActiveSheet.Cells(1, 1).CellType = dt

    And if you want to show 2PM in the cell, then replace the hour parameter of TIME formula with 14 in cell(1,1) .Thanks

Need extra support?

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

Learn More

Forum Channels