Forum Discussion

DawnaEve's avatar
DawnaEve
Copper Contributor
Jul 01, 2023

Date Format with Hyphens

I'm transcribing a genealogical project with dates ranging from the 1600 to present.  I formatted the date column to show the date as DD-MMM-YYYY, but any date 100 years or more will not show the hyp...
excel
office 365
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jul 01, 2023

DawnaEve 

The issue you are experiencing with the date format not displaying hyphens for dates 100 years or more in the range from the 1600s to the present is likely due to a limitation of the date formatting in Excel.

When you use the custom date format "DD-MMM-YYYY", Excel interprets the hyphens as separators between day, month, and year. However, Excel's date system has a cutoff point of January 1, 1900. Dates prior to that are considered serial numbers, where 1 represents January 1, 1900.

Therefore, when you have dates before January 1, 1900, Excel does not recognize them as traditional dates and will not display the hyphens in the custom format. Instead, it treats them as serial numbers and displays them accordingly.

To work around this limitation and ensure consistent formatting with hyphens for all dates, you can consider using a text format instead of a date format.

Here is what you can do:

  1. Select the date column.
  2. Right-click on the selected range and choose "Format Cells."
  3. In the Format Cells dialog box, select the "Text" category.
  4. Click "OK" to apply the text format to the date column.

With the text format applied, you can enter the dates in the format you desire, such as "DD-MMM-YYYY," and Excel will retain the hyphens without considering them as separators for its date system.

Keep in mind that using the text format means Excel will not recognize the values as dates for date-related calculations or sorting. However, for genealogical projects where the emphasis is on displaying and transcribing the dates accurately, using the text format should meet your requirements.

 

Of course, if you want it as date input and not text, you can use VBA as well. Attached is an example file with the self-made formula in VBA to be able to enter the date before 1900. Detailed information in the file. The text and steps were created with the help of AI, the "VBA formula" and the file was created/inserted by me.

My answers are voluntary and without guarantee!

 

Hope this will help you.

Resources