Excel FAQs: Number & general formats

Having covered font formats including the use of Excel Styles and Themes and the formatting of numbers in the first two parts of this formatting series, in this concluding part we will look at the formatting of dates and times and miscellaneous entries, explains Simon Hurst.

What standard date and time formats are available?

The list of Categories on the number tab of the Format Cells dialog includes ‘Dates’ and ‘Times’. Of the list of possible date and time formats, those preceded by an asterisk are affected by the Control Panel, Regional and Language settings. We will look at the date formats here, but the time formats work in the same way:

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Date and month without the year

alan thornton | | Permalink

Is there any way of getting Excel to accept a day and month without including a year?

It seems that whatever is displaying (and the year can easily be left out of the display), the software assumes that it is in the current year when the entry is first made unless you enter a different year.

For instance, on a sheet with client year ends dates, where the actual year is irrelevant and even unhelpful, Excel assumes that the year is the one when the client was first entered on the list. If new ones have been added in later years, then when the list is sorted by date, these new ones appear in a completely different part of the sequence from others with the same year end. I've ended up having to enter all new clients with a year end in 2008, which is not always remembered!

shurst's picture

Hi Alan

shurst | | Permalink

Hi Alan

Excel dates are held as serial numbers starting with 1.00 for 1 January 1900 at midnight. Time is the decimal bit so 1.33 is 8am on 1 January 1900. So all dates are evaluated as if they include a year component. Hopefully, someone will come up with a really smart way of doing what you want, but all I have thought of so far would be to enter all of them with the same year - say 1900 or even 00 (which will be 2000 by default). You can change the format to only show days and months. Alternatively, for sorting, add a new column with a formula such as:

=DATE(0,MONTH(F6),DAY(F6))

This will, in effect, treat all the years of the dates entered so far as 1900.

Or, forget that they're dates and enter them as text in the format mm dd so that they sort as required, but this will make it less easy to treat them as dates for other purposes.

Year end without year - sorting

tim hervey | | Permalink

I see what you mean! If you use dd-mmm, it adds the current year (2012) but doesn't display it.

One way round this would be to use two columns instead of one for the day and month, one headed Day and one headed Month. Assuming you have one  headed Client, you can then set up a sort rule to apply to the range of data being used by clicking the Sort button on the toolbar and in the Sort window: Sort by row: Month; Sort On: Values; Order: Use custom list = January, February, March etc.

Then click Add level and in the Then by row: Day; Sort On: Values; Order: Smallest to Largest.

Then click Add level and in the Then by row: Client; Sort On: Values; Order: Ato Z. Click OK.

If you don't have headers, jsut uncheck the My data has headers in the Sort window.

You should no whave a lsit of clients order by their name within chronological year ends.