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 :)
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
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
Thank you for patiently reading through the Contents. Will try to update sooner on Part 3 :)
No comments:
Post a Comment