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.
We can access some quick date and time formats from the Home tab > in the Number group:
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.
- Press CTRL+1, or right-click > Format Cells to open the Format Cells dialog box.
- 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/2016 | 3 | |
| dd | Displays the day as a number with a leading zero when appropriate. | 3/09/2016 | 03 | |
| ddd | Displays the day as an abbreviation (Sun to Sat). | 3/09/2016 | Sat | |
| dddd | Displays the day as a full name (Sunday to Saturday). | 3/09/2016 | Saturday | |
| m | Displays the month as a number without a leading zero. | 3/09/2016 | 9 | |
| mm | Displays the month as a number with a leading zero when appropriate. | 3/09/2016 | 09 | |
| mmm | Displays the month as an abbreviation (Jan to Dec). | 3/09/2016 | Sep | |
| mmmm | Displays the month as a full name (January to December). | 3/09/2016 | September | |
| mmmmm | Displays the month as a single letter (J to D). | 3/09/2016 | S | |
| yy | Displays the year as a two-digit number. | 3/09/2016 | 16 | |
| yyyy | Displays the year as a four-digit number. | 3/09/2016 | 2016 | |
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
Note: if your PC region settings have the Date & Time formats set to show the Short Time as hh:mm tt or the Long Time as hh:mm:ss tt then this may override any single 'h' formats and display them as 'hh'.
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
Custom cell formatting isn't limited to dates and times. There is a plethora of formatting options for all types of numbers that we can use to get our reports looking just the way we want. Click here for our comprehensive guide to Excel custom number 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.
Josh N
Below is my current battle…
Sample cell – 11/26/2021 1:24pm
Custom format – m/d/yyyy h:mm am/pm
After entering the custom format for the column, I click sort and I am still getting AM times mixed into my PM times…
11/26/2021 1:55pm
11/26/2021 1:57pm
11/26/2021 10:14am
11/26/2021 10:18pm
11/26/2021 10:26am
11/26/2021 10:32am
11/26/2021 10:33pm
11/26/2021 10:36pm
11/26/2021 10:38am
What am I missing here?!
Philip Treacy
Hi Josh,
Hard to be sure without seeing your data but I’m guessing your data is actually text, not date/time. Press CTRL + ` and you should see the serial numbers for the dates/times. If you don’t, it’s text and you need to convert it to date/time. Try using Power Query or refer to this https://www.myonlinetraininghub.com/fixing-excel-dates-formatted-text
Regards
Phil
Jorge Herrera
[$-x-systime]h:mm:ss AM/PM
Can u explain this formula?
Mynda Treacy
Hi Jorge,
It’s a custom number format. The [$-x-systime] is a culture tag specifying the system time format, as explained here.
Mynda
Gael
Can I add times? If I enter start and end times, is there a formula that will translate that into minutes spent?
Mynda Treacy
Hi Gael,
If you want to show the cumulative time in minutes then you simply use a custom number format: [m]:ss
Mynda
Gael
Thanks, Mynda, I appreciate you taking the time to answer, but I have no idea how to translate what you’ve told me into a formula in my spreadsheet. If you could speak slowly (all I know about Excel I’ve picked up on my own; I’m absolutely just winging it) or provide a link to an example?
Mynda Treacy
In this tutorial under the heading Custom Excel Date and Time Formatting I cover the steps for putting that format in your worksheet. If you’re still stuck, please post your question and Excel file on our forum where we can help you further.
Geoff
I’ve downloaded a csv file that has a time on it but when its imported just looks like a number ie 10:46 ,if I open the CSV in Excel and use a formula on it it shows as the decimal equivalent of the number is no longer the same time but rather a decimal representation of it and doesn’t hold the 10:46 i require ,this isn’t then comparable with other sheets that have the correct time format from elsewhere
I cannot get to change to a time format that equals the original downloaded number/time
Thank you
Geoff
someone has kindly solved this for me on another site thank you
Mynda Treacy
Glad you found a solution, Geoff.
Pat
I can’t find any way to put a date and time with am, pm into one cell, without it being a timestamp.
I am creating a spreadsheet for keeping track of medicines I take. (I have a crazy dr that makes me bring all my meds in every appointment and have them counted.) So take some meds at wake-up, lunch and bedtime. I currently have a cell for the date of the last appointment and the date of the next appointment with another cell calculating the number of days between the two. I was wondering if there is a way I could put the time and date of each appointment in the cells and somehow create a formula that would count how many pills I took and had left according not just the days between appointments but somehow configuring the time of the appointments. I hope I am describing this correctly. Thanks!!!
Catalin Bombea
Hi Pat,
Can you please upload your sample file on our forum? We will be able there to help you with a personalized solution using your file.
yehia abo elnaga
Please i am looking how to extract the number day of any date ( year days 365)
for example ( today is 20/11/2019 ) = (number day is (323) from 365 day)
thanks
Catalin Bombea
Try:
=A1-DATE(YEAR(A1),1,1)
yehia abo elnaga
thanks for your support
it’s (Done)
but if i need the reverse of the equation
for example ( number day is 327) = ( so the result is the date ( 23/11/2019 ) ).
Catalin Bombea
That should be obvious, in excel 1 day equals one unit. If you add to 01/01/2019 date the 327 number, that’s all.
If you have the date in a cell, a basic addition is enough: =A1+327
yehia abo elnaga
Thank you very much
Aldas Baltutis
i am looking for the elegant way to calculate night and day power consumption. the problem is that night tariff is calculated from 23:00 to 7:00, and counter indications are not taken daily.
e.g.
26 October, 13:06
28 October, 11:26
4 November, 17:46
how to calculate the time (hours and minutes) that can be attributed to the night/day time?
Catalin Bombea
Is it enough if you multiply the number of days by 8?
=CEILING(a2-a1,1)*8
I assume you have dates in a1 and A2, not text (26 October, 13:06 is missing the year).
Daniel A DePasquale
when downloading information to upload into my accounting software the date is displayed as 01/15/2019 Wed. How can i get rid of the text without doing it individually? I’ve tried to play with the date formatting but haven’t been successful.
Mynda Treacy
Hi Daniel,
You can use Power Query to split the column into the Date and week day name, or you can use Text to Columns.
Mynda
Sherman
What if you don’t want your output in military (24-hour time)? I’m doing a value of how many times something is mentioned and converting that value into 15 minute increments. So for example, if something was mentioned twice, that would convert to a half an hour aka 30 minutes.
I want the display to just display 0:30 to reflect 30 minutes. Or if something is mentioned 4 times, the output should be 1:00 or one hour.
I don’t need an actual time of day, just a time value to represent the sum of time. Any help here is appreciated.
Mynda Treacy
Hi Sherman,
You can apply this number format:
[h]:mm
Mynda
Francisco
I have a doubt related to how time format and the data input formatting can be locked in excel.
An excel time schedule need to be filled by a team in a certain cells i.e. Start and End columns and the time by lines [12:00] [13:00].
Although, the cells are formatting [hh:mm] the team not comply with this format and it is usual to have data inputted as 12h30m, 12.30, 12,30 which retrieves a lot of errors in the linked tables.
How can I avoid the wrong format data input by the team in the time schedule sheet, can I lock the format in that cells in the way to retrieve an error or not allows to input data unless it is with the right format, something like a drop box in data validation excel feature?
Many thanks.
Mynda Treacy
Hi Francisco,
You can use Data Validation to force users to input values in a specific format: https://www.myonlinetraininghub.com/excel-drop-down-lists
Mynda
OMAR ALSHARIF
so many thanks for this effort.
You are so amazing.
I noticed that in the Date & Time 101 work sheet, there is a typing mistake.
You wrote: (24 hours or half of a day, and 1 hour is 0.41666′ because it’s 1/24 of a day.)
It is 0.041666 not 0.41666
Kindly correct this also in the PDF.
Regards.
Mynda Treacy
Thanks, Omar. Well spotted. I’ve made those amendments.