Custom Formating - Excel Date Format

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

You can view them as you want based on how do you format the cells. There are predefined date formats available when you choose format cells option. However you can use custom format option to display dates if available formats does not serve your requirement.



You can choose following codes or combination of it to view date in excel as you want. I have used highlighted codes dd mm yyyy with "/" to show today's date as 16/02/2009 in above screen. You can use any of these code singly or jointly with any character in between.

Excel Vlookup function - Entering column number

Many users find it difficult to count and enter column number in Excel vlookup formula. This looks more difficult when the number of columns in the data range are large and gets complecated in case some of columns are hidden. However Microsoft Excel has inbuilt feature by which it helps you to know the column number. At the time of selecting table array it shows you the number of columns selected. If you notice this at the time of selection, you actualy do not need to count the number of columns. This works even if you have hidden columns in your data. Pls see video for further explanation.

Entering last day of the month in Excel

Entering a specific date in excel is easy as you know what to enter, however if you are asked to enter last date of the month it becomes little tricky as last date of the month varies between 28 to 31 depending on what year and month it is.

However Excel date mathematics makes it easy. What you need to do is enter the first day of next month and then reduce it by 1, you will get last day of previous month.

Look at following picture to understand it better.


I have used date function to enter Feb 1, 2009 and then reduced the day by 1, other wise you can enter =Date(2009,2,0) also to get same result. after you have added above formula in Cell B4 you can use this formula to enter last day of next months in cell B5 =DATE(YEAR(B4),MONTH(B4)+2,0). You can further copy this down to get next month.