Working with Date and Time Formats

Working with Date & Time Formats

Excel stores a lot more than just text and numbers. It can store dates and times, too – together in the same cell or in separate cells. It also offers you the ability to set up custom date and time formats for your unique needs.

First, let’s go to a currently empty cell and type a date. I’ll type May 12, 2021 in cell G5. When I type that, Excel’s format for the cell, previously set to General (and displayed in the Number section of the ribbon), is now “Custom” – and a day, month, year format is applied.

If I type 5/12/2022 in cell G6, the format applied is the same as what I typed, and the format showing on the ribbon in the Number section is Date.

If I only type 5/12, as I am in cell G7, Excel applies Custom as the format, and displays 12 May.

Obviously, if you have very specific needs for how your dates appear, you’ll want to choose a format ahead of time – or apply it to existing dates – using the Number format drop list, and choose from either Short Date or Long Date – and I’ll demonstrate both choices here, typing the same date into 2 different cells and then applying Short Date to the first and Long Date to the second – here in cells G8 and G9.

Another way to format your dates is to open the Format Cells dialog box, by either clicking the Number Format button in the corner of the Number group on the ribbon or choosing Format Cells from the pop-up menu when you right-click a cell or selected range of cells.

NOTE! You want to select the range to which the formatting should be applied, regardless of how you apply the formatting, BEFORE you apply the format – this saves you applying formatting over and over throughout all the dates in your worksheet, and assures you more consistency among dates that should appear the same way.

Here, in the Format Cells dialog box, if I click Date in the Category list on the left, I see several choices in the Type list. I can click on any one of them to apply the format, and if the selected cell or cells already contained a date, I can see my date in the Sample box.

You can also choose Custom from the Categories list and create any custom date format you’d like – using d, m, and y characters for day, month, and year, and using whatever separators you’d like to use – dashes, slashes, etc…

You can type the custom format into the Type box and see the Sample showing the result.

You probably noticed that some of the Date formats offered in the Format Cells dialog box included time formatting – and we can apply those if we have a cell containing both a date and a time, or we can apply time formats to cells that contain only the time.

The same procedure for applying format works here, too – choose one from the Number drop list on the ribbon, or use the Format Cells dialog box to select one. You can also set up custom time formats using h, m, and s characters, and separate them each with a colon – that’s the most widely-understood symbol to use for times.

In my worksheet, I’ll use the Format Cells dialog box to format the Start Time and End Time columns creating a Custom hh:mm AM/PM format, as we don’t care about seconds. That gives us 2 characters for hours and minutes, and then indicates AM or PM.

If you work on a 24-hour clock, that’s a format you can easily apply, too. From the Time options in the Format Cells dialog box, choose 13:30 (or any of the other formats that begin with 13). As shown here, if I enter a time such as 11:15 pm, it’s converted to a 24-hour clock time once that format is applied to the cell.

So now you’re ready to store and format dates and times in your worksheets, accurately reflecting when your sales were made, hours worked, taxes paid, and all your other time-sensitive information, too.