Ronald ross p. G. College (mba) koheda road, mangalpally (V) ibrahimpatnam (M), R. R. (Dist) 505510

Date Formulas and Functions

• 
  Today's date and time: =NOW()
  

• 
  Today's date only: =TODAY()
  

• 
  Day of the month for a specific date: =DAY(TODAY())
  

• 
  Day of the week for a specific date (set the cell format to custom format "dddd"):
  

=WEEKDAY(TODAY())

=TEXT(WEEKDAY(TODAY()),"dddd")

• 
  Month of the year for a specific date: =MONTH(TODAY())
  

• 
  Year for a specific date: =YEAR(TODAY())
  

• 
  Date for a specific year, month and day (Example returns 6/12/2005):
  

=DATE(2005,6,12)

• 
  Add days to a date (one week from today): =TODAY()+7
  

• 
  Subtract days from a date (one week ago today): =TODAY()-7
  

• 
  Last day of the this month: =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
  

or

= EOMONTH(TODAY(),0)

• 
  Last workday of the current month:
  

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1,Holidays)

• 
  First workday of the following month:
  

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1,Holidays)

• 
  Number of workdays in the current month:
  

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Holidays)

• 
  Last workday in range of days (A1 is start date, B1 is number of days in the period): =WORKDAYS(A1,B1,Holidays)
  

DATEDIF() Syntax & Examples

The DATEDIF() function uses the following syntax:

=DATEDIF(start_date,end_date,"code")

The start date must be less than the end date, or the function returns an error.

The following are the codes for the DATEDIF() function:

• 
  "y" Years
  
• 
  "m" Months
  
• 
  "d" Days
  
• 
  "md" Difference between days in a period; no month and years
  
• 
  "ym" Difference between the months in a period, no days and years
  
• 
  "yd" Difference between the days in a period, no years
  

Examples

• 
  To calculate the number of years between two dates:
  

=DATEDIF(A1,TODAY(),"y") & " Years"

• 
  To calculate the number of years and months between two dates:
  

=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months"

• 
  To calculate the number of years, months, and days between two dates:
  

=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months, " & DATEDIF(A1,TODAY(),"md") & " Days"

Using the TEXT() function to calculate dates.

You can use the TEXT() to get the number of days or weeks between two dates:

Examples

• 
  Number of days:
  

=VALUE(TEXT((NOW()-$A$1)/24,"[h]"))

• 
  Number of weeks:
  

=VALUE(TEXT((NOW()-$A$1)/168,"[h]"))
Using Date and Time Functions in Excel 2002

Have you ever wondered how long it has been since the first day of 1900? Maybe you haven’t, but the designers of Microsoft Excel decided to base their time and date functions on this day. Trying to determine the date exactly 1000 days from today without a computer requires the use of several calendars and a lot of time. With the use of a spreadsheet program, such as Excel, the exact day can be displayed in moments. In this article, you will examine and use many of the date and time functions built into Excel.

When a date is entered into a worksheet cell, Excel immediately converts the date into a serial number. January 1, 1900 is serial number 1 and the serial number of the date entered is the number of days since January 1, 1900. In Figure 1, three dates entered into cells in column A have been copied into column B. The only difference is that the dates in column B appear in the General format rather than a Date format.



Figure 1

Regardless of the format of the date, the value in memory is the serial number. Using date and time functions and arithmetic operators you may find the difference between two dates, determine the day of the week three months from the current day, or find the number of workdays within a given range of dates, excluding Saturday, Sunday, and holidays. Date and time functions in Excel fall into several categories, each of which is explored in the following sections.

Just Give Me the Number

Type in a date, as in 1/18/2002, and Excel immediately converts it into a date serial number. But what if you have the month, day, and year in different cells? The Date function requires three arguments, the month, the day, and the year. You can type in the pieces of the date directly into the function, or use the cell addresses containing the components. Figure 2 illustrates both ways to use the Date function. Note that the order of the arguments is the year, followed by the month, and finally the day. If you enter the date directly into the function, each of the arguments must be enclosed within quotation marks.

Figure 2

The Datevalue function works in much the same way, except it only has one argument — the date you want to convert, enclosed within quotation marks, in the yyyy/mm/dd format. An example is DATEVALUE(“2001/11/14”).

Excel can also display the time with or without the date. As far as Excel is concerned, the time is no more than a fraction of a day. For instance, .5 days is 12 noon, .25 is 6 a.m., and so on. You can use the Time and Timevalue functions to display the time either by entering the time directly into the function or by referencing the time in cells. Figure 3 shows the Time functions, and the result in both the General and the Time format. The Timevalue function lets you enter the time as hours, minutes, seconds as in TIMEVALUE(“10:15:00”) for 10:15 a.m.



Figure 3

There are two special functions useful when you want your worksheet to display the current date and/or time whenever the worksheet is opened. The Today function displays the current day in the mm/dd/yyyy format. Although you must type in the parentheses after the function, you must not enter any arguments. Therefore, TODAY() will display 2/13/2002 on February 13, 2002, 12/9/2005 on December 9, 2005, and so on. The Now function does the same thing as the Today function, except the result is formatted to display the current time as well as the current date. Like the Today function, the Now function has no arguments, as in NOW().

Give Me a Piece of That
Sometimes you have to take a date serial number and convert it into a value that is more meaningful to people. For example, the Day function displays just the day of the month of a date, regardless of the format of the date. Likewise, the Month, Year, Hour, Minute, and Second functions extract a portion of the date or time. Figure 4 illustrates each of these functions.



Figure 4

When Will That Be?

The above functions are the “plain vanilla” functions essential to many worksheets. Using these functions, and others, you can display and calculate the difference between dates, add or subtract days from a given date, and so on. But there are a number of specialized functions that perform tasks that would otherwise require many calculations and logical operations. For instance, the Weekday function returns the day of the week in which the date in the argument falls, 1 being Sunday, 2 Monday, 3 Tuesday, and so on. Another function, Weeknum, tells you the number of the week that a date falls within.

Then there are a group of functions that use two dates in their arguments, especially handy in accounting applications. Days360 figures out the number of days between two dates, based on a 360-day calendar (12, 30-day months). Yearfrac determines the fraction of a year represented by the two dates in the argument. Figure 5 shows examples of these functions.



Figure 5

Accounting solutions may depend on determining the date a specified number of months away or the last day of that month. Edate uses two arguments — the start date and the number of months until the same day of another month. For example, the Edate of 2/7/2003 with a second argument of 2 would be 4/7/2003 — the same day two months away. If the second argument is a negative number, the answer is a date in the past. So, EDATE(B12,-3) would be a date three months before the date in cell B12.

Eomonth works just like Edate except it finds the last day of the month. Just as with Edate, the arguments are the start date and the number of months before or after the start date. Therefore, EOMONTH(C15,5) would return the date May 31, 2002 if the start date in C15 was any day in January 2002.

Finally, there are two date functions that can be very useful to accounting and human resource departments, among others — Workday and Networkdays. The Workday function finds the next workday from a specified date, excluding holidays. The function has three arguments — the start date, the number of days before or after the start date, and a range of cells containing holidays that should not be considered as work days. Networkdays calculates the number of work days between a start date and an end date, again excluding holidays. Check out the examples in Figure 6 to see these amazing functions in action.