Date & Text Functions
In this unit you will
learn how to:
- Find smarter ways to calculate dates and times
using TODAY, NETWORKDAYS, WORKDAY and DATEDIF
- Use Text functions UPPER, PROPER, LEFT, RIGHT, LEN,
MID, SEARCH and FIND
- Use TYPE to identify the data type of cell contents
- Use TRIM to remove excess spaces in cells
Calculating Dates and Times using TODAY, NETWORKDAYS, WORKDAY
and DATEDIF
TODAY
The TODAY function displays the current date. Because it
updates to show the new current date it is often used for date calculations
that change with time such as a person’s age.
Syntax
=TODAY()
NETWORKDAYS
This function calculates the number of working days (not
weekend dates) between two dates.
Syntax
=NETWORKDAYS(StartDate,EndDate,Holidays)
Holidays is a list
of dates which will be excluded from the calculation such as public holidays.
They can be entered as range of cells or as a list “25/12/12”,”01/”01/13”etc.
This example calculates the net working days between two
dates for employees taking annual leave.
WORKDAY
This function calculates a future or past date based on a
starting date and a specified number of working days. One of its uses could be
to calculate invoice due dates or delivery dates from an order date.
Syntax
=WORKDAY(StartDate,Days,Holidays)
In this example the function calculates a delivery date
based on an order date and an estimate of the number of days till delivery.
DATEDIF
Another useful function for calculating intervals between dates is DATEDIF. It is one of Excel’s mysteries why DATEDIF does not appear in the drop-down list of functions. You must always enter it manually. The function originates from Lotus 1-2-3 and is no longer documented in Excel Help.
DATEDIF is useful for calculating the number of days, months and years between two dates.
Syntax
=DATEDIF(FirstDate,SecondDate,"Interval")
FirstDate must be
earlier than SecondDate or an error
is returned.
Interval is as
follows:
|
"d" |
Days between
the two dates. |
|
"m" |
Months
between the two dates. |
|
"y" |
Years between
the two dates. |
|
"yd" |
Days between
the dates, as if the dates were in the same year. |
|
"ym" |
Months
between the dates, as if the dates were in the same year. |
|
"md" |
Days between
the two dates, as if the dates were in the same month and year. |
For example suppose you want to know someone’s age in years.
The formula using DateDif would be:
=DATEDIF(C8,TODAY(),"y")
To include the number of months type:
=DATEDIF(C8,TODAY(),"ym")
And to include te number of days:
Putting these together the age in years, months and days
would be:
0 Comments