Date and Time function in Excel
This step-by-step article describes how to use date and time functions in Microsoft Excel to perform complex date and time calculations. Excel stores all dates as integers and all times as decimal fractions.
To get started
Creating a date functions in Excel
Step 1 : Create a Workbook
Step 2: Type the following function in cell A1
=today()
Step 3 : Press <Enter> and Function returns todays date in active cell
After you finish the step Excel will appear as shown in above screen shot.
Note: Short cut Key combination for adding the current date is ctrl +:Creating Date and Time function
Step 1 : Create a workbook
Step 2 : Type the following function in cell A1
=now()
Step 3: Press <Enter> and Function returns today’s date and time in Excel
After you finish the step Excel will appear as shown in above screen shot
Note: If you want to change the formatting of date Right Click –Select date under Number tab – change to your required format.Creating categories in date functions.
To find the MONTH, DAY, YEAR, DATE, DATEVALUE, TEXT separately, follow these steps
Step 1: Create a workbook
Step 2: Type the following in cell B5.
= today()
After you finish this step, excel will appear as shown in above figure
Step 3: To find the month separately from current date, type the following in cell B7
= month(B5)
Above screen shot shows the number of months from date in B5 cell.
Step 4: To find the DAY separately from current date, type the following in cell B8.
=day(B5)
Above screen shot shows the number of days from date in B5 cell.
Step 5: To find the YEAR separately from current date, type the following in cell B9.
=year(B5)
Above screen shot shows the year from date in B5 cell.
Step 6 : By date function we can combine the month, date, year together. For example, below screen shot shows the year, month and day mentioned in cell E6, F6, G6.
Type the following in cell B10
=date(E6,F6,G6)
Above screen shot shows the date from the combination of year, month and day in different cell.
Step 7: By DATEVALUE function, you can convert the date in the form of text to a number. Type the following in cell B11.
=datevalue(b7&”/”&b8&”/”&b9)
After you finish this step, excel will appear as shown in above screen shot.
The DATEVALUE function comes in handy when you have a list of dates stored as text in your worksheet and you want to sort or filter all these dates or use these dates in date calculations.
Step 8: By TEXT function you can convert value to a text in a specific number format. Type the following in cell B12
=Text(b5,”mmmm,dd,yyyy”)
After you finish the steps excel will appear as shown above screen shot.
Based on the Excel spread sheet above TEXT function would return the following.
| =TEXT(B12, “$#,##0.00″) | would return “$41,394.00″ |
| =TEXT(B12, “0″) | would return “41394″ |
| =TEXT(B12, “0.0″) | would return “41394.0″ |
| =TEXT(B12, “#,##0″) | would return “41,394″ |
| =TEXT(B12, “yyyy/mm/dd”) | would return “2013/04/30″ |
| =TEXT(B12, “mmm dd, yyyy”) | would return “Apr 30, 2013″ |