DATE Functions
Excel DATEDIF Function
Introduction
The Excel DATEDIF function returns the difference between
two date values in years, months or days. The DATEDIF (Dated + if) function is
a "compatibility" function that comes from Lotus 1-2-3. For reasons
unknown it is only documented in Excel 2000 but you can use it in your formulas
in all Excel versions since that time. Excel won't help you fill out the arguments
for DATEDIF like other functions but it will work when configured correctly.
Purpose
Get days, months or years between two dates.
Return value
A number representing time between two dates
Syntax
=DATEDIF (start_date, end_date, unit)
“Y” means Year. But if we mention here “M” then it gives the
answer. “M” for Month
If we want to check the Days between two dates then the
result will be
“D” means Days.
Arguments
· start_date - Start date in Excel date serial
number format.
· end_date - End date in Excel date serial number
format.
· unit - The time unit to use (years, months or
days).
Valuable notes
The DATEDIF function calculates the time between a start date and an end date in years, months or days. The time unit to return is
specified using the unit argument which is supplied as text (upper or lower
case). The list below summarizes available unit values and the result for each:
Unit Result
"Y" for Difference in complete years
"M" for Difference in complete months
"D" for Difference in days
Notes
1. Excel
will not help you fill in the DATEDIF function like other functions.
2. DATEDIF
with throw a #NUM error if start date is greater than the end date. If you are
working with a more complex formula where start dates and end dates may be
unknown or out of bounds you can trap the error with the IFERROR function or
use MIN and MAX to sort out dates.
Get months between dates
Formula
=DATEDIF(start_date,end_date,"m")
Explanation
To calculate months between two dates as a whole number you
can use the DATEDIF function. In the example shown the formula in B4 is:
=DATEDIF(B2,B3,"m")
Remember that “unit” is not case-sensitive.
Note
The DATEDIF automatically rounds down. To round up to the nearest month see below.
The mystery of DATEDIF
As told earlier that the DATEDIF function is a
"compatibility" function that comes from Lotus 1-2-3. For reasons
unknown DATEDIF is only documented in Excel 2000 and will not appear as a
suggested function in the formula bar. However, you can use DATEDIF in all
Excel versions since that time you just need to enter the function manually.
Excel will not help you with function arguments.
How this formula works
DATEDIF takes 3 arguments: start date, end date and unit. In
this case, we want months, so we apply "m" for unit. DATEDIF
automatically calculates and returns a number for months rounded down.
Nearest whole month
DATEDIF rounds down by default. If you want to calculate
months to the nearest whole month, you can make a simple adjustment to the
formula:
=DATEDIF(start_date,end_date+15,"m")
This ensures that end dates occurring in the 2nd half
of the month are treated like dates in the following month effectively rounding
up the final result.
Excel date
In Excel's date system, dates are serial numbers. January 1,
2021 is number 1, January 2, 2021 is number 2 and so on. More recent dates are
much larger numbers. For example, January 1, 2010 is 40179. Because dates are
just numbers, you can easily perform arithmetic on dates. For example, with the
date October 8, 2021 in B2, you can add 10 days like this: =B2+10 or 44477+10 =44487
or October 18, 2021 and subtract 7 days: =B2-7 or 43983-7 =44470 or October 01,
2021
or
Because Excel dates are serial numbers, you'll sometimes see
these numbers on a worksheet when you expect a date. To display date values in
a human-readable date format, apply a number format of your choice. To check
that Excel is correctly recognizing a date, you can temporarily format the date
as a number. If Excel doesn't display the date as a number it means the date is
not correctly recognized.
Dates not recognized
A common problem in Excel is that dates are not correctly
recognized usually because Excel thinks the dates are text.
Create date with DATE function
You can use the DATE function to create a date with a formula
using individual year, month and day components. For example, the following
formula creates the date "October 08, 2021":
=DATE(2021,10,08)
Dates with Times
Dates can include times as well since time values are just fractions of a 24-hour day. To create a date with a time using a formula you can use the DATE and TIME function together. For example, the following formula creates the date value for "October 08, 2021 9:00 PM":
=DATE(2020,3,10)+TIME(21,0,0)
Remember that Excel will only handle dates after 1/1/1900.
Excel DATE Function
The Excel DATE function creates a valid date from individual
year, month and day components. The DATE function is useful for assembling
dates that need to change dynamically based on other values in a worksheet.
Purpose
Create a date with year, month and day
Return value
A valid Excel date
Syntax
=DATE (year, month, day)
Arguments
· year - Number for year
· month - Number for month
· day - Number for day.
Notes
The DATE function creates a valid Excel date using
individual year, month and day components.
In this way, the formula is
The result is
The DATE function is useful for assembling dates that need
to change dynamically based on other values.
The answer is
The DATE function is especially useful when supplying dates as inputs to other functions like SUMIFS or COUNTIFS, since you can easily assemble a date using year, month and day values that come from a cell reference or formula result. For example, to count dates greater than January 1, 2021 in a worksheet where A2, B2 and C2 contain year, month and day values respectively, you can use a formula like this:
=COUNTIF(range,">"&DATE(A2,B2,C2))
The result of COUNTIF will update dynamically when A2, B2 or
C2 are changed.
Notes
· Excel dates begin in the year 1900. If year is
between zero and 1900, Excel will add 1900 to the year
· Month can be greater than 12 and less than zero.
If month is greater than 12, Excel will add month to the first month in the
specified year. If month is less than or equal to zero, Excel will subtract the
absolute value of month plus 1 (ABS(month) + 1) from the first month of the
given year
· Day values can be positive or negative. If day
is greater than the days in the given month, Excel will add day to the first
day of the specified month. If day is less than or equal to zero, Excel will
subtract the absolute value of day plus 1 (ABS(day) + 1) from the first day of
the specified month.
Extract date from a date and time
Formula
=INT(date)
Explanation
Excel handles dates and time using a scheme in which dates
are serial numbers and times are fractional values.
Notes:
· With either method above, make sure you use a
date format on the result that does not include a time. Otherwise, you'll see
the time displayed as 12:00 AM
· For dates and times (which must be positive in
Excel) there is no difference in using INT and TRUNC to extract an integer. But
INT actually rounds numbers down to the nearest integer which makes a
difference when values are negative.
Get days between dates
Formula
=later date - earlier date
Explanation
Calculate the number of days between two dates you can
simply subtract the older date from the newer date. If want to see only the
days then right click on the cell where to apply the formula and select Format
Cells from the Pop up menu
When the Format Cells dialog box will open, go to Custom
option in the Category
Here, in the Type box, you can see dd-mm-yy. Delete mm-yy i.e. for month and year, only type dd (for
date).
Press OK button, the date will change into days in the Excel
sheet
Working with today
If you need to calculate the number of days between an earlier date and today, use:
=TODAY()-earlier date
If we wish to describe the No. of days instead of date then
we have to convert the date into text. TODAY will recalculate on an on-going
basis. If you open the workbook at a later date, the value used for TODAY will
update and you will get a new result.
Get project end date (WORKDAY)
Formula
=WORKDAY(start,days,holidays)
Explanation
To calculate a project end date based on a start date and
duration, you can use the WORKDAY function.
Right click on the cell where you apply the formula and
select Format Cells from the pop up menu.
When the Format Cells dialog box will open, choose the Date
option from the Category
Here choose the format, which you want, from the Type option
and click on OK button. The result will display on the excel sheet
How this formula works
This formula uses the WORKDAY function to calculate an end
date. WORKDAY can calculate dates in the future or past and automatically
excludes weekends and holidays (if provided).
Different workdays
The WORKDAY function has a hardcoded notion of weekends,
always treating Saturday and Sunday as non-working days. If your schedule has
different requirements, you can substitute the WORKDAY.INTL function for
WORKDAY. For example, if workdays are Monday through Saturday, you can specify
this by providing 11 as the third argument in WORKDAY.INTL like this:
=WORKDAY.INTL(C5,D5,11,holidays)
Next anniversary date (EDATE)
Formula
=EDATE(date,(DATEDIF(date,as_of,"y")+1)*12)
Explanation
To calculate the next anniversary date, you can use a formula based on the EDATE and DATEDIF functions. This formula will work to calculate next upcoming birthday as well.
How this formula works
Working from the inside out, we use the DATEDIF function to
calculate how many complete years are between the original anniversary date and
the "as of" date where the “as of” date is any date after the
anniversary date:
As we are interested in the next anniversary date, we add 1
to the DATEDIF result then multiply by 12 to convert to years to months.
Same here, open the Format Cells dialog box and choose Date
from Category. The result will be
Add days to date
Formula
=date+days
Explanation
You can directly find the next date. As
Excel EDATE Function
Introduction
The Excel EDATE function returns a date on the same day of
the month, n months in the past or future. You can use EDATE to calculate
expiration dates, maturity dates and other due dates. Use a positive value for
months to get a date in the future and a negative value for dates in the past.
This function is used as for FD date, insurance date or
pollution date.
Purpose
Shift date n months in future or past.
Return value
New date as Excel serial number.
Syntax
=EDATE (start_date, months)
Arguments
· start_date - Start date as a valid Excel date
· months - Number of months before or after start_date.
Notes
Use EDATE to get the same date in future or past months. For
months, use a positive value for future dates and a negative value for dates in
the past. EDATE will also respect leap years:
Get year from date
Formula
=YEAR(date)
As in the following example =YEAR(A2)
A2 contains a date value. The YEAR function returns the
number 2021 representing the year of the date.
Explanation
If you need to extract the year from a date, you can use the
YEAR function. In the generic form of the formula above, the date must be in a
form that Excel recognizes as a valid date.
How the formula works
The YEAR function takes just one argument, the date from
which you want to extract the year. You can use YEAR to extract the year from a
day entered as text. However, using text for dates can cause unpredictable
results on computers using different regional date settings. In general it's
better and more flexible to supply an address to a cell that already contains a
valid date.
Comments
Post a Comment