Displaying data entry place holders and delimiters during data entry

[Davmacrat]

Hi all,

Have been designing and maintaining a project for a friend for some years, and now refining how data is entered by the users to allow maximum ease and efficiency. The user currently keys a date and time in the format of dd/mm/yyyy hh:mm. This is currently set up in a custom cell format. There will be thousands of these entered at regular intervals over time.  So the current most efficient method of entering the displayed date and time of 09/03/2023 23:10 is to enter 9/3/23 23:10. To take this to the next level, when the user clicks or double clicks on a cell I am looking to display the place holders and delimiters i.e.

I _ / _ _ / _ _  _ _ : _ _ . I want to also create an input behaviour such that when the users start typing they replace the data place holders i.e. the '_' with their input, but jump across the delimiters, i.e. the '/ and :' leaving them in place to be displayed as per the format, hence for example if the user keyed 2310221800 this would be displayed as 23/10/2022 18:00. However the difficult bit is to have the users input still subsequently treated as a Date and Time by the excel system for calculations. 

Can someone advise if this is even possible?

Could it be done with an input form?

Thanks for your help 

Kindest regards

D.

[#2]

@Davmacrat 

It is possible to create an input form in Excel that can help users enter data in a specific format.

You can use VBA (Visual Basic for Applications) to create a UserForm that displays the placeholders and delimiters for date and time entry.

The UserForm can be programmed to accept user input and automatically format it as a date and time value.

This value can then be used in calculations within the Excel workbook.

You can use the BeforeUpdate event of the TextBox to format the entered text as a date and time. For example, you can use the Format function to change the date to a standard format. Another option is to add a Date and Time Picker control to your UserForm. This control allows users to select a date and time from a calendar-like interface. You can then use the Change event of the Date and Time Picker control to validate the entered date and time and ensure that it falls within the desired range. 

Another solution to create a data entry form in Excel without using VBA is to use Excel’s built-in data entry form feature. This feature allows you to enter data in a form structure and can be accessed by adding the Form command to the Quick Access Toolbar or the Ribbon . Once you have added the Form command, you can use it to enter data into an Excel Table. The data entry form displays each field in a row in a vertical layout and allows you to navigate between records, enter new data, and edit or remove existing entries .

I hope this helps!