Date and Time Custom Formatting in Microsoft Excel

"My excel is not showing date properly."

"Excel is now getting into my nerves."

if these are your regular comments on excel then you are on right page. sometimes entering or formatting date in excel can be annoying.

lets see how can we change our above mentioned comment.

1) How do I Enter date in a excel Cell?

- you can enter date in proper format in a cell by only maximum Five keystrokes. (!)

  Lets see if it is possible..

Suppose I want to enter 10-May-2013 (I have to remember this date to stay alive.. hope you understand).

Select any cell in excel and type "10/5" and hit enter.

And you will see something like this. (if you see different result try  - 5/10, Results differ because of location settings). I have set my location to India.

but wait, hold your donkeys. it is not showing year. for that just press Ctrl+shift+3.

it will convert to the dd-mmm-yy. 

here, you dont need to type year while entering date for current year as excel is taking year from your system time.

2) How to change formatting to see only day or full month name?

-  to apply custom formatting to a cell select that cell and right click on that cell and select formatting cells option.

A dialog box will open like this:

Then you can type into highlighted box below mentioned formats from column "Custom formatting" for desired results.

ddd	Sr no	Desciption	Without Formatting	Custom	Value after formatting
	1	Convert to "dd-mmm-yy" format	19-02-13	dd-mmm-yy	19-Feb-13
	2	Should show only day (Only first three Letters)	19-02-13	ddd	Tue
	3	To see only day	19-02-13	dddd	Tuesday
	4	Should show only Full Month	19-02-13	mmmm	February
	5	Should show only year	19-02-13	yyyy	2013
	6	Should show only Date and Day	19-02-13	dd dddd	19 Tuesday
	7	Add text "My Favourite Number is " Before the number shown in C11	12	"My Fav no is "0	My Fav no is 12
	8	Add +91 Prefix	9393939393	"+91"0	+919393939393
	9	Convert to 10 digit number with "0" as prefix	123456	0000000000	0000123456
	10	Convert to Nearest Fraction	0.12368	?/?	1/8

You will get Results mentioned in Value after formatting column. 

I hope this will solve your date problem (!). Happy Dating in excel.

If you have any questions or queries please reply.