Tag Archives: format cells

Tame your computer – deal with dates

14 Mar

When you enter a date such as 4/3 in Excel,  the default date format is 04-Mar. You can quickly reformat it using the drop-down button in the Number group on the Home tab and select Short Date or Long Date, but what if you also want to display the day? For example, Tuesday 14 March 2017? Some people use the Text Function to in a separate column (=TEXT(A1,”ddd”) but there is a way to format dates to include the day of the week.

Here’s how:

  1. Right-click the cell containing the date(s) or the whole column.
  2. Select Format Cells from the menu.
  3. If necessary (probably not) display the Number tab.
  4. Select the Custom option in the Category box.
  5. In the Type box, double-click on the word General and type the custom format of your choice. (Mine is ddd dd mmmm yyyy.)
  6. Press ENTER or click OK

Herewith some other options you might like to try out in step 5  … to get Tuesday 12 December 2017 when you type 12/12, set the Custom format as dddd dd mmmm yyyy. Or if you prefer to see it as Tue 12/12/17 try out ddd dd/mm/yy. The underlying date you typed won’t change and can be checked by looking in the Formula Bar.

Related tips:
Display your numbers with leading zeros : http://roem.co.uk/tip_193.html
Convince Excel you want to type July 2010 : http://roem.co.uk/tip_313.php
Save time entering dates : http://roem.co.uk/tip_442.php

Advertisements

Tame your computer – countdown to Christmas

30 Sep

You might have used Excel’s TODAY worksheet function to automatically enter the current date. But did you know you can also use this Function to create a countdown to the given date of your upcoming events such as a holiday, anniversary, birthday, wedding, retirement, completion of house … or Christmas?

Here’s how:

  1. Enter the date of the event (say, 25/12/12) in, say, cell A1
  2. Select cell B1 and type =TODAY()
  3. Select the cell in which the countdown result is to appear (say, C1) and type =A1-B1
  4. If necessary, right-click over cell C1, select Format Cells and choose General (from the Number tab)

If you save the file as normal you can check every day how many days are left between now and your special day. (See http://www.roem.co.uk/Xmascountdown.xls for an example.)