You are reading the article Excel Date And Time Formatting updated in September 2023 on the website Chivangcangda.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Excel Date And Time Formatting
Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want.
Custom Excel Date and Time Formatting
We can also create our own custom date and time formats to suit our needs. Let’s take a look.
Select the cell(s) containing the dates you want to format.
On the Number tab select ‘Date’ in the Categories list. This brings up a list of default date formats you can select from in the ‘Type’ list. Likewise for the Time category.
We aren’t limited to the defaults though. You can create your own Custom date or time formats in the ‘Custom’ category. These custom formats are saved for you to re-use in the current file.
Custom Date Formatting Characters
Excel recognises the following characters and sets of characters for date formatting.
Character Explanation Date Formatted d Displays the day as a number without a leading zero. 3/09/2023 3 dd Displays the day as a number with a leading zero when appropriate. 3/09/2023 03 ddd Displays the day as an abbreviation (Sun to Sat). 3/09/2023 Sat dddd Displays the day as a full name (Sunday to Saturday). 3/09/2023 Saturday m Displays the month as a number without a leading zero. 3/09/2023 9 mm Displays the month as a number with a leading zero when appropriate. 3/09/2023 09 mmm Displays the month as an abbreviation (Jan to Dec). 3/09/2023 Sep mmmm Displays the month as a full name (January to December). 3/09/2023 September mmmmm Displays the month as a single letter (J to D). 3/09/2023 S yy Displays the year as a two-digit number. 3/09/2023 16 yyyy Displays the year as a four-digit number. 3/09/2023 2023
Custom Date Formatting Examples
We can bring the characters together to create our own custom formats. Some examples below:
Remember; the custom format doesn’t alter the underlying date serial number, it is still the same.
Custom Time Formatting Characters
Like dates, time also has its own set of custom formatting characters, as listed below:
Character Explanation h Displays the hour as a number without a leading zero. [h] Displays elapsed time in hours. If you are working with a formula that returns a time in which the number of hours exceeds 24, use a number format that resembles [h]:mm:ss or [h]:mm hh Displays the hour as a number with a leading zero when appropriate. If the format contains AM or PM, the hour is based on the 12-hour clock. Otherwise, the hour is based on the 24-hour clock. m Displays the minute as a number without a leading zero.* [m] Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss. mm Displays the minute as a number with a leading zero when appropriate.* s Displays the second as a number without a leading zero. [s] Displays elapsed time in seconds. If you are working with a formula that returns a time in which the number of seconds exceeds 60, use a number format that resembles [ss]. ss Displays the second as a number with a leading zero when appropriate. If you want to display fractions of a second, use a number format that resembles h:mm:ss.00. AM/PM, am/pm, A/P, a/p Displays the hour using a 12-hour clock. Excel displays AM, am, A, or a for times from midnight until noon and PM, pm, P, or p for times from noon until midnight.
*Note: The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
Custom Time Formatting Examples
The screenshot above is what I see with my PC region settings for the Short Time as h:mm tt. If you see something different when using a single ‘h’ format, then it will be down to your PC region settings.
More Excel Formatting
Free eBook – Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel – Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the Excel Workbook and PDF . Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.
You're reading Excel Date And Time Formatting
Update the detailed information about Excel Date And Time Formatting on the Chivangcangda.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!