Cookies help us deliver our services. By using our services, you agree to our use of cookies.

Blue Flower

Article applies to: BO4.1 JRE

When creating a custom format directly on a cell, traditionally, you'll right-click on the cell and choose Format Number, click on the Custom button and create your format. With dates, there's an extra step - simply click on the Date/Time format category first, then click on the custom format button or you'll end up seeing all your dates as simply Mmm yyyy instead of Feb 2017 and so on!

In terms of popular date formats, here are a few common ones and what they represent (based on the current date/time being 17:11 on Feb 14th 2017):

  • dd/MM/yyyy gives 14/02/2017 (be careful; dd/mm/yyyy would give you 14/11/2017 - mm = minutes, MM = months)
  • d/M/yyyy gives 14/2/2017 - a single d or M drops the leading 0 from the date
  • yyyy-MM-dd gives 2017-02-14, which looks like SQL Server date format (and lends itself to sorting when it becomes text)
  • MM/dd/yy gives 02/14/17, a more common format for our American friends
  • hh:mm gives 05:11, while HH:mm gives 17:11 and hh:mm a gives 05:11 PM
  • Mmm yyyy gives Feb 2017, while MMM yyyy gives FEB 2017 and Mmmm gives February 2017

As you can imagine, you can tweak different parts of this to get to the date format that you require.