PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Microsoft Excel Time Formatting
Here is a simple Delphi procedure that opens a new Microsoft Excel file (i.e., Excel workbook) and demonstrates how to apply a variety of time formatting options. The file is then saved. The most relevant parts of the code are highlighted. Comments are included to explain the code. If you copy and paste the code into your program, be sure to change the form and procedure names to match your setup. We recommend that you run the code and open and examine the Excel file generated. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
procedure TForm1.MicrosoftExcelTimeFormatting(Sender: TObject);
var
ExcelFileName: String;
ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant;
begin
//be sure ComObj and Variants units are included in the "uses" clause

ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file_time_formatting.xlsx'; //replace file name with the name of your file

ExcelApplication := Null;
ExcelWorkbook := Null;
ExcelWorksheet := Null;

try
//create Excel OLE
ExcelApplication := CreateOleObject('Excel.Application');
except
ExcelApplication := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelApplication) = False then
begin
try
ExcelApplication.Visible := True; //set to False if you do not want to see the activity in the background
ExcelApplication.DisplayAlerts := False; //ensures message dialogs do not interrupt the flow of your automation process. May be helpful to set to True during testing and debugging.

//Open Excel Workbook
try
ExcelWorkbook := ExcelApplication.WorkBooks.Add(-4167);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
//https://docs.microsoft.com/en-us/office/vba/api/excel.xlsheettype
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelWorkbook) = False then
begin
//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
try
ExcelWorksheet := ExcelWorkbook.WorkSheets[1]; //[1] specifies the first worksheet
//reference
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheets.Item
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;

//add text to A1 cell [row,column]
ExcelWorksheet.Cells[1,1] := 'Microsoft Excel - Cell Number Formatting - Time Formatting';
//change the font size in A1 cell
ExcelWorksheet.Cells[1,1].Font.Size := 20;
//make A1 cell font bold
ExcelWorksheet.Cells[1,1].Font.Bold := True;
//change font color to blue
ExcelWorksheet.Cells[1,1].Font.Color := clBlue;
//change background color of A1 cell
ExcelWorksheet.Cells[1,1].Interior.Color := clYellow;

//add text to A3 cell [row,column]
ExcelWorksheet.Cells[3,1] := 'Hold your mouse over cells with numbers to see comments.';

//add text to A5 cell [row,column]
ExcelWorksheet.Cells[5,1] := 'Time Format';
ExcelWorksheet.Cells[5,1].Font.Bold := True;
ExcelWorksheet.Cells[5,1].Interior.Color := clAqua;

//add text to B5 cell [row,column]
ExcelWorksheet.Cells[5,2] := 'Format Code';
ExcelWorksheet.Cells[5,2].Font.Bold := True;
ExcelWorksheet.Cells[5,2].Interior.Color := clAqua;

//add text to C5 cell [row,column]
ExcelWorksheet.Cells[5,3] := 'Format Description';
ExcelWorksheet.Cells[5,3].Font.Bold := True;
ExcelWorksheet.Cells[5,3].Interior.Color := clAqua;

//add date and time, code, and description to row 6 [row,column]
ExcelWorksheet.Cells[6,1] := '=now()'; //adds today's date to A6 cell
ExcelWorksheet.Cells[6,1].AddComment('Default date and time format');
ExcelWorksheet.Cells[6,3] := 'Default date and time format';

//add date and time, code, and description to row 7 [row,column]
ExcelWorksheet.Cells[7,1] := '=now()';
ExcelWorksheet.Cells[7,1].NumberFormat := 'm/d/yyyy hh:mm:ss AM/PM';
ExcelWorksheet.Cells[7,1].AddComment('Date and time format: month/day/year hour:minute:second. 1 or 2 digits for month and day. 2 digits for hour, minute, and second. AM/PM format.');
ExcelWorksheet.Cells[7,2] := 'm/d/yyyy hh:mm:ss AM/PM';
ExcelWorksheet.Cells[7,3] := 'Date and time format: month/day/year hour:minute:second. 1 or 2 digits for month and day. 2 digits for hour, minute, and second. AM/PM format.';

//add date and time, code, and description to row 8 [row,column]
ExcelWorksheet.Cells[8,1] := '=now()';
ExcelWorksheet.Cells[8,1].NumberFormat := 'm/d/yyyy h:m:s AM/PM';
ExcelWorksheet.Cells[8,1].AddComment('Date and time format: month/day/year hour:minute:second 1 or 2 digits for month and day. 1 or 2 digits for hour, minute, and second. AM/PM format.');
ExcelWorksheet.Cells[8,2] := 'm/d/yyyy h:m:s AM/PM';
ExcelWorksheet.Cells[8,3] := 'Date and time format: month/day/year hour:minute:second 1 or 2 digits for month and day. 1 or 2 digits for hour, minute, and second. AM/PM format.';

//add date and time, code, and description to row 9 [row,column]
ExcelWorksheet.Cells[9,1] := '=now()';
ExcelWorksheet.Cells[9,1].NumberFormat := 'mm/dd/yyyy hh:mm:ss';
ExcelWorksheet.Cells[9,1].AddComment('Date and time format: month/day/year hour:minute:second. 2 digits for month and day. 2 digits for hour, minute, and second. 24-hour time format.');
ExcelWorksheet.Cells[9,2] := 'mm/dd/yyyy hh:mm:ss';
ExcelWorksheet.Cells[9,3] := 'Date and time format: month/day/year hour:minute:second. 2 digits for month and day. 2 digits for hour, minute, and second. 24-hour time format.';

//add date and time, code, and description to row 10 [row,column]
ExcelWorksheet.Cells[10,1] := '=now()';
ExcelWorksheet.Cells[10,1].NumberFormat := 'hh:mm:ss AM/PM';
ExcelWorksheet.Cells[10,1].AddComment('Time format: hour:minute:second. 2 digits for hour, minute, and second. AM/PM format.');
ExcelWorksheet.Cells[10,2] := 'hh:mm:ss AM/PM';
ExcelWorksheet.Cells[10,3] := 'Time format: hour:minute:second. 2 digits for hour, minute, and second. AM/PM format.';

//add date and time, code, and description to row 11 [row,column]
ExcelWorksheet.Cells[11,1] := '=now()';
ExcelWorksheet.Cells[11,1].NumberFormat := '[$]hh:mm:ss a/p"m";@';
ExcelWorksheet.Cells[11,1].AddComment('Time format: hour:minute:second. 2 digits for hour, minute, and second. am/pm format (lower case).');
ExcelWorksheet.Cells[11,2] := '[$]hh:mm:ss a/p"m";@';
ExcelWorksheet.Cells[11,3] := 'Time format: hour:minute:second. 2 digits for hour, minute, and second. am/pm format (lower case)';

//add date and time, code, and description to row 12 [row,column]
ExcelWorksheet.Cells[12,1] := '=now()';
ExcelWorksheet.Cells[12,1].NumberFormat := 'hh:mm:ss';
ExcelWorksheet.Cells[12,1].AddComment('Time format: hour:minute:second. 2 digits for hour, minute, and second. 24-hour time format.');
ExcelWorksheet.Cells[12,2] := 'hh:mm:ss';
ExcelWorksheet.Cells[12,3] := 'Time format: hour:minute:second. 2 digits for hour, minute, and second. 24-hour time format.';

//add date and time, code, and description to row 13 [row,column]
ExcelWorksheet.Cells[13,1] := '=now()';
ExcelWorksheet.Cells[13,1].NumberFormat := 'hh:mm AM/PM';
ExcelWorksheet.Cells[13,1].AddComment('Time format: hour:minute. 2 digits for hour and minute. AM/PM format.');
ExcelWorksheet.Cells[13,2] := 'hh:mm AM/PM';
ExcelWorksheet.Cells[13,3] := 'Time format: hour:minute. 2 digits for hour and minute. AM/PM format.';

//add date and time, code, and description to row 14 [row,column]
ExcelWorksheet.Cells[14,1] := '=now()';
ExcelWorksheet.Cells[14,1].NumberFormat := 'hh:mm';
ExcelWorksheet.Cells[14,1].AddComment('Time format: hour:minute. 2 digits for hour and minute. 24-hour time format.');
ExcelWorksheet.Cells[14,2] := 'hh:mm';
ExcelWorksheet.Cells[14,3] := 'Time format: hour:minute. 2 digits for hour and minute. 24-hour time format.';

//add date and time, code, and description to row 15 [row,column]
ExcelWorksheet.Cells[15,1] := '=now()';
ExcelWorksheet.Cells[15,1].NumberFormat := 'General';
ExcelWorksheet.Cells[15,1].AddComment('Time format: General. Date and time are shown as a number and fraction.');
ExcelWorksheet.Cells[15,2] := 'General';
ExcelWorksheet.Cells[15,3] := 'Time format: General. Date and time are shown as a number and fraction.';

//add date and time, code, and description to row 16 [row,column]
ExcelWorksheet.Cells[16,1] := '=now()';
ExcelWorksheet.Cells[16,1].NumberFormat := '[$]hh:mm;@';
ExcelWorksheet.Cells[16,1].AddComment('Time format: hour:minute. 2 digits for hour and minute. 24-hour time format.');
ExcelWorksheet.Cells[16,2] := '[$]hh:mm;@';
ExcelWorksheet.Cells[16,3] := 'Time format: hour:minute. 2 digits for hour and minute. 24-hour time format.';

ExcelWorksheet.Cells[17,1] := '=now()';
ExcelWorksheet.Cells[17,1].NumberFormat := '[$-F400]h:mm:ss AM/PM';
ExcelWorksheet.Cells[17,1].AddComment('Time format set to default system format.');
ExcelWorksheet.Cells[17,2] := '[$-F400]h:mm:ss AM/PM';
ExcelWorksheet.Cells[17,3] := 'Time format set to default system format.';

//references
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.numberformat
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat
//https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68?ui=en-us&rs=en-us&ad=us
//https://learn.microsoft.com/en-us/office/vba/api/Access.format.propertydate.time
//https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

//autofit the width of Column A
ExcelWorksheet.Columns[1].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[2].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[3].EntireColumn.AutoFit; //autofits Column A
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit

//select A1 cell
ExcelWorksheet.Cells[1,1].Select; //selects Cell A1

ExcelWorkbook.SaveAs(ExcelFileName);
//Note: If a file with the new name already exists, it overwrites it. Write additional code to address as desired.
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;

ExcelWorksheet := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;






Posted: Saturday, November 19, 2022