As shown in the attached pictures below, I am trying to replicate the custom number format feature where the user can add text strings to dates i.e. yyyy" x "mm" y "dd" z " resulting in a cell displaying 2017 x 10 y 26 z but it still behaves just like a normal date would (meaning the function box still shows 2017-10-26).
I have a user form with six textboxes, three for the date format and three for text strings (if any) (not a pretty UI, I know). I also have a seventh textbox that displays the resulting date with text as it should be displayed in the cell (by combining the content of the other textboxes).
The first problem is that I could not find any solution on the web where text string can be inserted in-between a date format. IsDate() / CDate() won't recognize the mix of numbers and text as a date. Numberformat = format(...) won't allow referencing textboxe.value or string as the origin of the format yyyy,mm,dd.
On top, I want to give the user the freedom to e.g. write just "17" instead of 2017 in the first textbox and the userform code to understand it as "yy", or, as another example, to leave the last text box empty and no text to be added at the end of the date.
Any ideas?
I can use something like this
Dim xDate As Date
xDate = IsDate(TextBox1.Value & "-" & TextBox3.Value & "-" & TextBox5.Value)
This can then be used for
ActiveCell.NumberFormat = Formats(xDate, ....)
But then I have to feed it the format and somehow also the strings.
Feature
Example user form
DateValue
function? techonthenet.com/excel/formulas/datevalue.php – Kostas K.