Convert dates using Arabic numerals to dates with Roman numerals
Q. Is there a way to format dates with Roman numerals in Excel? For example, can I convert April 15, 2023, to 15-IV-2023?
A. You can convert dates written with Arabic numerals to dates with Roman numerals. If you only had one date to convert, doing this manually would probably be easier. However, if you have several dates, there are steps to convert them all. Let’s start by looking at an example like the one described in the question above. You can download the Excel workbook used in this article here and access a video demonstration of how to do this conversion at the bottom of this article.
Below is a screenshot of a snippet of the dates we will be converting in our Excel spreadsheet.
If each date is in one cell, as they are in the spreadsheet above, they will need to be dissected first. This can be done quickly with Text to Columns. Select all the cells that contain the dates in A2:A154 in the accompanying workbook. Click the Data tab on the Ribbon, choose Text to Columns from the Data Tools group, and the Convert Text to Columns Wizard window will open. Choose Delimited under Original data type and click Next. Under Delimiters, choose Other: and enter / in the box to the right. This instructs Excel to separate each cell anytime a / appears, which is what separates the elements of each date. Click Next. Choose General under Column data format and click Finish. You will need to change the formatting of the values in column A from Date to General. All values will already be selected once you click Finish. Go to the Home tab and change Date to General in the Number group.
The screenshot below shows a snippet of the spreadsheet after the dates have been dissected.
Next, we will use the ROMAN function to convert portions of the dates with Arabic numerals to Roman numerals. The syntax for the ROMAN function is ROMAN(number, [form]), where number, which is required, is the cell that contains the Arabic numeral to be converted, and form, which is optional, is the type of Roman numeral. Leaving this last argument blank will specify the classic Roman numeral.
The question above asked about a conversion to “Arabic day”-“Roman month”-“Arabic year.” We will join each of the three date elements using “&,” along with the ROMAN function to convert the month. The formula that will be entered into cell E2 is =(B2)&”-”&ROMAN(A2)&”-”&(C2). This formula pulls the day of the date that is entered in cell B2, as is; inserts a hyphen; pulls the month from A2 and converts it to a Roman numeral; inserts another hyphen; and finally pulls the year from C2, as is. Drag this formula down for all dates. The screenshot below shows the final date results.
You can use these same concepts to convert dates using Arabic numerals to dates using Roman numerals in other ways as well. For example, you may want to convert the dates using Arabic numerals to all Roman numerals. You would need to first dissect the dates using Text to Columns as described above. In cell E2, the formula that should be entered is =ROMAN(B2)&”-”&ROMAN(A2)&”-”&ROMAN(C2). Drag this formula down for all dates. The screenshot below shows the final date results.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
