The Date Function

If you have ever come across loads of data in a spreadsheet involving dates or inventory ageing, you have probably come across some of the things described below. We will wade through these common scenarios and applications one by one. Some of the formulas used in this context might seem unique or rather a little crazy while others may seem quite generic. We will take up the not so unfamiliar DATE functionality in this post.

And you may have possibilities that outperform the ones described here. Please feel free to use the comments section for that purpose.

 

Now to the DATE function : Microsoft defines it as unique sequential serial number that returns on a particular date.

^Syntax = DATE(Year, Month, Day)

You may write 30-August-2014 as DATE(2014, 8, 30) and if you change the date format to a number format it should give 41881.00^[1]

 

 

 

 

1.1) When does this function help ?

You may have at times encountered data in which dates are not in a ‘excel recognizable’ format in cases like downloads from ERP software, internet banking or text dumps in CSV formats among others. And Data –> text to columns for a date format might not work on them.

 

And in many such cases you would have experienced that selecting the format as date dropdown doesn’t work as seen in the below image. It might be quite tedious to correct multiple dates, and that’s exactly where DATE as a function comes in handy.

 

1.2) How ?

1.2.1 - We use some text functions mainly LEFT, RIGHT & MID to extract the necessary strings.

 

^Syntax = LEFT(Select Text Cell, number of characters to be extracted from Left)

^Syntax = RIGHT(Select Text Cell, number of characters to be extracted from Right)

^Syntax = MID(Select Text Cell, Start number for 1st character to be extracted from Left, number of characters to be extracted after that)

 

Using the text formulas nested within the DATE function we can extract the required date and consequently we can use it to do things like DATE ARITHMETIC – Add, Subtract, Find Difference, Find the Calendar Day or next working day among others, which shall be discussing in another post.

 

1.3) I want to have it in another format. How do I do that?

One, if you want to have a different format such as MM/DD/YYY or a standard format in the same cell you can right-click on the cell A7 and select Format Cells and navigate to Date in the side tab as shown here.

Alternatively, you can drop down the number formatting tab and select ‘More Number formats’.

For a custom format, one can choose custom from the left pane and type in the required date formatting syntax.

 

^DF.Syntax DD – Numerical Date

^DF.Syntax DDD – Day (Monday, Tuesday…)

^DF.Syntax MM – Numerical Month (01-January, 02- February…)

^DF.Syntax MMM – First three letters of the name of the month (Jan – January)

^DF.Syntax MMMM – Complete name of the Month

^DF.Syntax YY – Last two digit of the year

^DF.Syntax YYY – Complete year

 

1.3.1) If you wish to take a specific formatting for display only purpose to another cell then you may use the TEXT function. However, date operations or algebra (we shall take it up in a later post) will not be possible for a text output.

^Syntax = TEXT(Select Reference Cell, “Required formatting within double quotes”)

Formatting for the date should be kept within double quotes with the DF.Syntax i.e date formatting syntaxes discussed above.

 

1.4) What if I enter day, month beyond the normal range of 31 and 12, or negative values.
Excel would subtract (negative days/months) or add (beyond 30 or 31 days, 12 months) from the entered date and give the final date.
Here it takes 31 days for August and adds the rest 14 days to hop to 14-September.
[1] As it stores date starting with 01-Jan-1900 as 1. So negative arguments will give erroneous results if calculation takes the date prior to the year 1900.

1.5) TODAY() gives today’s date as in the computer. It’s dynamic and will change everyday.
^Syntax = TODAY()