Microsoft Office Tutorials and References

In Depth Information

example of a formula that uses the DATEDIF function (it assumes cells A1 and A2 contain a date). The formula

returns the number of complete years between those two dates.

=DATEDIF(A1,A2,”y”)

The following table displays valid codes for the third argument. You must enclose the codes in quotation marks.

The start_date argument must be earlier than the end_date argument, or the function returns an error.

Here's a similar formula that returns the day of the year for the current date:

=TODAY()-DATE(YEAR(TODAY()),1,0)

The following formula returns the number of days remaining in the year from a particular date (assumed to be

in cell A1):

=DATE(YEAR(A1),12,31)-A1

When you enter this formula, Excel applies date formatting to the cell. You need to apply a non-date number

format to view the result as a number.

To convert a particular day of the year (for example, the 90th day of the year) to an actual date in a specified

year, use the formula that follows. This formula assumes that the year is stored in cell A1 and that the day of the

year is stored in cell B1.

=DATE(A1,1,B1)

Determining the day of the week

The WEEKDAY function accepts a date argument and returns an integer between 1 and 7 that corresponds to

the day of the week. The following formula, for example, returns 3 because the first day of the year 2013 falls

on a Tuesday:

=WEEKDAY(DATE(2013,1,1))