VBA & Macro Problems

How to Enter Date and Time Digits Without Separators in WPS Spreadsheets

John WilsonJohn Wilson Nov 13, 2026 868 views

Question details

The user wants to type a ten-digit date-time value such as 2310221800 into a worksheet and have editable placeholders or automatic conversion create a valid date and time.

How to Enter Date and Time Digits Without Separators in WPS Spreadsheets
Product
WPS Spreadsheets
Device & OS
not provided
Scenario
Entering date and time records quickly without typing separators such as slashes, dashes, or colons.
Observed behavior
Normal cell entry in WPS Spreadsheets does not show __/__/____ __:__ placeholders or automatically interpret ten digits as a date-time value; custom formats only apply after a valid value exists.
Before you start

Open the workbook where date-time entry is needed and make sure the Developer tab is available in WPS Spreadsheets so you can access the Visual Basic editor and insert a UserForm.

Solution 1Recommended

Create a VBA UserForm to Accept Ten-Digit Date and Time Entry

Use this when you need editable placeholders and automatic conversion of values such as 2310221800 into a real date-time value.

A UserForm can display an input mask with __/__/____ __:__, validate the typed digits, and write a true date-time serial to the worksheet. The example below uses DateSerial and TimeSerial to build the value from a two-digit year.

1
Open the Visual Basic editor

In WPS Spreadsheets, open the workbook, click the Developer tab, and select Visual Basic; if the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

2
Insert and design the UserForm

In the Visual Basic Editor, click Insert > UserForm. Add a TextBox for the ten-digit input, a Label that displays __/__/____ __:__, and a CommandButton named btnConvert.

3
Add the validation and conversion code

Double-click btnConvert and add code that checks Len(TextBox1.Text) = 10 and IsNumeric(TextBox1.Text), then uses DateSerial(2000 + CInt(Left(TextBox1.Text,2)), CInt(Mid(TextBox1.Text,3,2)), CInt(Mid(TextBox1.Text,5,2))) + TimeSerial(CInt(Mid(TextBox1.Text,7,2)), CInt(Right(TextBox1.Text,2)), 0) to write the converted date-time to ActiveCell.

4
Run the UserForm and enter digits

Press F5 with the UserForm selected, type a ten-digit value such as 2310221800, and click the Convert button to place the valid date-time value in the selected worksheet cell.

Create a VBA UserForm to Accept Ten-Digit Date and Time Entry
Placeholder entry achieved: The UserForm shows the mask before input and converts the digits into a valid serial date-time that you can format with a date/time number format.

Use a VBA UserForm in WPS Spreadsheets to Enter Date and Time Digits Without Separators

Enable a VBA UserForm in WPS Spreadsheets to display __/__/____ __:__ placeholders, accept ten-digit date-time input such as 2310221800, validate it, and write a true date-time value to the active cell.

  1. 1. Open the VBA editor in WPS Spreadsheets: Open your workbook, click Developer > Visual Basic, or press Alt+F11.
  2. 2. Create a UserForm with a placeholder text box: Insert a UserForm and add a TextBox plus a Label that displays __/__/____ __:__.
  3. 3. Add the conversion code: Use DateSerial and TimeSerial to parse digits like 2310221800 and write the result to ActiveCell.
  4. 4. Run the form and enter digits: Press F5, type the ten-digit value, and click the button to insert the converted date-time.
Creates editable placeholders for ten-digit date and time entryConverts input with standard DateSerial and TimeSerial functionsPreserves .xlsm macro workbook compatibility with Microsoft ExcelWrites a true serial date-time value that responds to number formatting
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't a custom number format show placeholders while typing?

A custom number format only changes how a cell appears after it contains a valid date-time serial value. It cannot create an editable input mask, so use a VBA UserForm or another form control for placeholder entry.

Can WPS Spreadsheets automatically convert 2310221800 into a date without VBA?

Normal cell entry does not automatically interpret ten digits as a date-time value. You can use formulas or VBA to split and convert the digits; for editable placeholders, a VBA UserForm is the most direct method.

Does this VBA method work in Excel files opened in WPS Office?

Yes. WPS Spreadsheets can run the UserForm and write a standard date-time serial, and the workbook can be saved as a macro-enabled .xlsm file for compatibility with Microsoft Excel.

What if the Date and Time Picker control is not available?

Use the text-box conversion method with DateSerial and TimeSerial. If you want the picker, check additional controls in the VBA Toolbox and confirm that the Microsoft Date and Time Picker control is registered on your system.

How do I change the displayed date-time format after conversion?

Select the cell and press Ctrl+1, choose Custom, and enter a format such as dd/mm/yyyy hh:mm or mm/dd/yyyy hh:mm to display the converted serial value.