Spread sheet cells to accept data only 24 hr time format

Spread sheet cells to accept data only 24 hr time format

Posted by: sandeepreddy778 on 19 November 2017, 11:16 pm EST

• 

  sandeepreddy778

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 19 November 2017, 11:16 pm EST

  Hi,
  
  i need to format the cells in spread sheet to accept only time in 24 hr format .
  
  could anyone please assist me .

  Thanks in advance.

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 20 November 2017, 6:23 pm EST

  Hello,

  You can set the celltype for the cell to DateTimeCellType and set the format to 24 Hrs Format. For example:

  
  FarPoint.Win.Spread.CellType.DateTimeCellType dt = new FarPoint.Win.Spread.CellType.DateTimeCellType();
  dt.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.UserDefined;
  dt.UserDefinedFormat = "HH:mm";
  
  

  Thanks,
  
  Deepak Sharma

• 

  sandeepreddy778

  • Post Options:
  • Link
    Copy

  Posted 28 November 2017, 5:31 pm EST

  Hi Deepak, thanks for the reply. i tried your solution, but the problem is for example when i enter
  
  4 in the cell value it is showing the minutes( 4:39) also without entering them and when i double click on the cell, time window is popping up. could you please provide the solution to restrict this.

  Thanks ,
  
  Sandeep.

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 28 November 2017, 9:26 pm EST

  Hi Sandeep,

  When you try to edit the cell it picks up the current time of machine and set accordingly. To disable automatic time fill up you can use the code as follows:

  
   FarPoint.Win.Spread.CellType.DateTimeCellType dt = new FarPoint.Win.Spread.CellType.DateTimeCellType();
              dt.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.UserDefined;
              dt.UserDefinedFormat = "HH:mm";
              dt.EnableSubEditor = false;
              fpSpread1.ActiveSheet.Cells[0, 0].CellType = dt;
              fpSpread1.EditModeOn += FpSpread1_EditModeOn;
  
   private void FpSpread1_EditModeOn(object sender, EventArgs e)
          {
              FarPoint.Win.Spread.CellType.GeneralEditor c = new FarPoint.Win.Spread.CellType.GeneralEditor();
              if (fpSpread1.Sheets[0].ActiveRowIndex == 0 && fpSpread1.Sheets[0].ActiveColumnIndex==0)
              {
                  c =(FarPoint.Win.Spread.CellType.GeneralEditor) fpSpread1.EditingControl;
                  c.Formatter = new FarPoint.Win.SuperEdit.GeneralFormatter();
              }
          }
  
  

  Also to not to show the Clock on double of DateTime cell you can disable the subeditor using the code:

  
  dt.EnableSubEditor = false;
  
  

  I hope it helps.

  Thanks,
  
  Deepak Sharma