Excel Tricks with Tejraj

Many times we are working with date and time values while working in excel. Many time we get data in which date and time is mentioned in a single cell (as shown in below image). But what if we want to separate the date as well time from this value? Well, we will learn to separate the date and time in this blog post whenever we will face similar situation. Basic Information: Before moving to this separation, first of all we need to understand some basic concepts related to date and time values which are available in excel. Dates are nothing but the integer values stored in excel. Whereas Times are the decimal values stored in excel.  For example: If we enter integer value 1 and change the cell format to dates, then it will become 01 January 1900.  If we enter the decimal value 0.59 and convert the cell format to time, then it will become 02:09 PM. Now, we will use these basic concepts to separate date and time from combined value. For this we will use INT function in excel . We...

In this blog post we will learn about DATE function in excel. When we have Year, Month and Day as a three separate values then with the help of DATE function we can convert them into single value which will represent a particular date. Where to find DATE function on Excel Screen: DATE function can be found under “Date & Time Function” category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Date & Time” category, we can see list of various Date & Time Functions available in excel. DATE function is highlighted in blue in below image. Once we click on DATE option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of DATE Function: The Syntax of DATE function is as below: =DATE(year, month, day) Arguments of DATE Function: To use the DATE function, we have to provide three arguments: year : In this argument we have to enter a number from 1900 or 1904 (depending upon workbook’s da...

In this blog post we will learn in detail about “Date Format in Excel”. There are many instances when we need to use dates in excel. If we know how to use Date Formats in excel then we can work very easily with the dates. In our previous blog post we have seen 03 methods to go to additional options of formatting cell values . If you missed to read those methods, please click here before going for this blog post. We can use any of these 03 methods to get the “Format Cells” dialog box and select “Date” Category as shown in below image. With this we can see all the additional options under “Date Format” in excel as shown in below image: Sample: This option is common in all the formats which we can see under Date Category in above image which we can use to see how the selected numbers will look in Date Format. Locale (Location): This option will convert the date into selected language based on a country. Type : We can choose any of the desired format under this option. The type which ...