All about Dates in excel - Part 2 : Date and Time Formatting

Hello Excellers,

In continuation to Part 1 - Date Entry, now we are going to see Part -2 Date Formatting. Now why is formatting so important ? Formatting is important with respect to the clients. Every client cares about the formatting equally as they care about the insight derived out of our excel service.

Now recalling part 1 for the date entry function which is inbuilt in excel. '=DATE(year,month,date)'. The output of this function is always going to be the default date format that you have setup in your system. Please find illustration below

In order to change the default displayed only in Excel, go to Home > Number > Format Drop down > More number formats or Right Click on the range you want to format > Select Format Cells

Please go through the (*), as they are the system default formats. Over here are the basic list of date formats. If you want to customize your formats, you can always choose 'Custom' in the Format Category. Say you want to display some random non-sensical format like 14-Jun/2015$, you do that by giving the following input: dd-Mmm/YYYY$.

We can use this to display the day name of a date also, by giving "DDDD" as the input. This can come really handy sometimes, as most people who are unaware of the trick use a weekday function and lookup over an array to return the day name with respect to the return of the weekday function. There is absolutely no harm in doing that, it is just that when you are handling a big data set with so many sub-optimal solutions, your end excel product will be very very slow. HENCE FORMATTING CAN SAVE A LOT OF DATASIZE OF THE EXCEL FILE AND INCREASE THE SPEED OF COMPUTING.

If the entered date has a time value also, using formatting one can choose to display only the time alone, still retaining the date value. Format Cells > Custom category > "h:mm"

One can also improvise to display the hour, minutes then the date or choose to display in 24 Hour format or the AM/PM format using these customizations

 So lets summarize now on using the formatting function

VERY IMPORTANT: Though the display format has changed, internally excel always retains the date/time value of the entered date/time in number formats.

Thank you for patiently reading through the Contents. Will try to update sooner on Part 3 :)

All about Dates in excel - Part 1 : Date Entry

Hello Excellers ( Just made that one up :P),

I think I am blogging after long time. Blogging skills might be outta shape, but definitely not the excel skills. We are going to see how to use date values/formats in excel. The flow of the article is split in to 4 parts - Date entry, Date Formatting, Date Manipulations and Date Format Conversions.

First of all, what is so special about dates in excel ? Why should we focus on it ?
Working with dates are like super-dooper important when you are working with your Clients. One has to adapt to the client's time zone and client's date formatting. I personally have felt a lot of nagging with my clients and my managers about the date formats. Trust me ! They always care about the formatting too. It is also sensible right ! Why should we communicate something which is out of context for the client.

Okay !! Enough stories. So, the first thing you are going to learn is how to adapt to your client's date formats in your excel work zone. One can change the entire systems date formats (including the task bar time/date display). Go to Windows->Control Panel->Region & Language->Choose your preferred date format.

Now this is going to be reflected in your excel environment also. How ?! Lets move to the next section.

Date Entry
According to excel, there is a numeric value assigned to each date. So what could be today's date Value. Try the following

1. Any cell '=today()'
• You can also enter date by 'Ctrl + ; ' and Enter key
2. Change format of the cell by Going to Home->Number->Format Dropdown-> Select 'Number'

So what are the fundas!!.

• 1 = 1 day

• So what will be the date value of 1 ? It's '1/1/1900'. I Believe most of the new users must have seen this somewhere
• The Day/month/year order depends on the system settings.
• Excel is so powerful that it can calculate the date of day zero :P

 

(Now, Stop trying for the date value of -1. It's gonna result in a hashtag of hashtags :D.)

• 1 = 24 hours
• So the value of 06:00 hours is 0.25, the value of 12:00 is 0.50 and you know what you are awesome, so you can improvise :)
• The standard time is in the format of  HH:MM:SS
• While entering both time and date together, you have to take note of the decimal point. After the decimal point 'Time value'. Before the decimal 'Date value'

So now that we have understood the funda, lets proceed further with date entry. Please note my system setting is in MM/DD/YYYY settings

So as mentioned in the table, always try to use the DATE and TIME function for entering any dates. There are risks of entering dates in a non-date format. For example if we want to enter 13th day of may month, it is wrong to enter it as '13/05/2015' if your system settings is in 'MM/DD/YYYY'. Excel will accept this data, but it will be accepted as texts.  

Note: You can identify whether it is a text data or date data by looking at the alignment. By default, All text data are aligned to the left end of the cell, while all numeric/date data are aligned to right.

The major problem an new excel user faces while working with dates is that they can't get a difference a between 2 dates. The reason is that it is in text. Excel will interpret your input as '=text1-text2' and thus will give a result #value . Damn we hate them !!

Also only if you have dates in the date/time format, functions like year, month, day, hour, minute & sec, are going to work on them. These we use for summarizing in any reporting.

So the GOLDEN RULE while working with dates in excel: ALWAYS HAVE THE DATES ENTERED IN THE DATE FORMAT.

Also you can't enter any dates before 1900 using the regular typing formats or the date function. It's gonna give weird results.  The archeaologists must be hating it right now !!

Please follow up Part 2 for date formatting. Thank you patiently reading through. Have a great Excel Day !!!!