PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
EXCEL HAS THE PROVISION OF DATES FROM JAN 1,1900 TO DEC 31, 9999 which corresponds to 2958465.
29/6 | 29 June 2020 |
29/6/2020 | 29 June 2020 |
29 JUNE 2020 | 29 June 2020 |
29 JUNE | 29 June 2020 |
29-06-2020 | 29 June 2020 |
29-06 | 29 June 2020 |
29 JUNE | 29 June 2020 |
29 JUN | 29 June 2020 |
2020-06-29 | 29 June 2020 |
JUNE-29-2020 | WRONG |
29-JUNE-2020 | 29 June 2020 |
29-Jun | 29 JUNE 2020 |
06/29/2020 | 29 JUNE 2020 |
JUNE 29,2020 | WRONG |
JUNE 29 | 29 JUNE 2020 |
06-29-2020 | 29 JUNE 2020 |
06-29 | 29 JUNE 2020 |
JUNE 29 | 29 JUNE 2020 |
JUN 29 | 29 JUNE 2020 |
2020-06-29 | 29 JUNE 2020 |
29 JUNE 2020 | 29 JUNE 2020 |
JUNE-29-2020 | 29 JUNE 2020 |
Few formats which seems to be correct but are not recognized by Excel are
JUNE 29 2020
JUNE 29,2020
JUNE-29/2020
in any of the date system.
One of the very tricky and important format.
EXCEL stores DATE as a serial number counted from JAN 1 1900. And converts this serial number into different types of date formats which we can choose from the given list.
Choose any format from the given list.
Sometimes a problem occurs when we can’t find the format of our choice. Mostly this is due to the location settings. Choose the correct location and you would find your desired format of date.
We can choose the location and EXCEL would populate different formats available from which we can choose our format.
Whenever we are working with the dates, which is going to be quite often, we should use date format for the ease which is provided by this format while using dates in our calculations.
In the picture above, we can see that we put the same fractional number , which we calculated and converted it to the Date and time using the TEXT FUNCTION. The function is visible clearly. The output comes out to be the same from where we had started i.e. July 1 2020 10 AM.
REMEMBER: THE TIME CALCULATION STARTS FROM THE 12:00 AM MIDNIGHT
By this we can find out any time in the day. Some of the standard fractions are .
TIME | FRACTION |
12:00 AM | 0 |
6:00 AM | 0.25 |
12:00 PM | 0.5 |
6:00 PM | 0.75 |
10:00 PM | 0.916666667 |
TIME ENTERED MANUALLY | TIME INTERPRETED BY EXCEL |
10 AM | 10:00:00 AM |
10:00 | 10:00:00 AM |
22:00 | 10:00:00 PM |
10:00 PM | 10:00:00 PM |
10:00:00 | 10:00:00 AM |
10:00:00 AM | 10:00:00 AM |
10:00:00 am | 10:00:00 AM |
10:30 | 10:30:00 AM |
These are just for the understanding. These can be easily found in Excel.
Again very tricky format and very important. The time is stored in excel as the decimal part of the number. For example 0.5 means 12 noon and so on. There are different formats of time from which we can choose how to show the time.
Choose the time format from the given list.
If somehow, can’t find the option of your choice, choose your location and the options will change. Now choose the desired one.
We should use time number format when we are working with the time in reports. It is advisable to understand the time format and use it efficiently and it’ll make our job extremely easy.
FOR OTHER DETAILS ABOUT NUMBER FORMATS CLICK HERE.
Many times, the current date and time are needed in our applications.
We can insert the current date and time easily.
While creating any report , we might come across a situation where we need to find a date after a fixed number of days from a given date.
While creating any report , we might come across a situation where we need to find a date fixed number of days ago from a given date.
Suppose a case where we have date in one cell and time in other cell. If we want to combine them into one cell, follow these steps.
Suppose we have date in one cell and time in other.
If we want to be sure that we face no problems while working with the dates, here is the best way to enter the dates.
Always enter the date using the DATE FUNCTION which is very helpful in handling the dates and we never go wrong with this function.
The syntax of the function is
The Syntax for the function is
=DATE(YEAR, MONTH, DATE)
DATE FUNCTION HAS THE CAPABILITY TO CONVERT THE TEXT SNIPPETS INTO DATE. WE NEED TO PASS THE TEXT ARGUMENTS IN THE DATE FUNCTION AND IT BECOMES A DATE.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE