|
Tips
on Excel Date and Time Functions and Formulas
If you enter the date
2/1/2005 in cell A1 and the number format of the cell is "General"
you will see 38394. This is a "Serial number" and it
is the way Excel works with dates and times. When you format
the cell or use one of the functions below the serial number is
viewed as times and dates
To enter the date
of the day no need to key it in, click and hold the CTRL key and
click on the semi-colon key (;) and there is the date.
To enter the time,
click and hold both the Shift and the CTRL keys and click on the
colon key (:) and there is the time.
Microsoft Excel doesn't
work with dates and times, it works with serial numbers This means
that when you enter 12/25/2004 Excel sees 38346 and if you
enter 12/26/2004 Excel sees 38347. When you enter 12:00:00 PM Excel
reads 0.5 and if you write 12:00:01 PM Excel reads 0.5000116. It
is when you format the cell "Format/Cells" that you can
read dates and times as we humans are accustomed to see them.
By the way, I was
born on 18373 at 0.25 so I am an Aries, and you?
This being said, most functions of
the category Date & Time are quite easy to work with when you
use the right cell format. For example, when you are adding times
and expect the total to be over 24 hours you
must set the format of the result cell to
"Format/Cells/Time/37:30:55"
If you develop a time
management application don't go through the trouble of working with
clock time. Ask your people to enter either the number of hours
worked by projects or the number of minutes then work with regular
numbers. Much easier.
Working
with dates and times without functions
The
three most important things that you should remember when working
with dates and times are:
FORMAT, FORMAT and FORMAT.
You
don't necessary need Excel functions of the date & time category
to work with dates. For example
Let's
say that you have the dates 1/16/2005 in cell A1 and 1/22/2005 in
cell B1
to add a day
is
:
=B1+A1
will return 3 if the format of the cell is either "General"
or "Number"
It will return 1/6/1900 if the format is
"Date"
With
the proper format if you have a date in cell A1 and you want the
date for the next day in cell B1 (formatted "date") the
formula will be:
=A1+1
to
calculate the date of a week later the formula will be:
=A1+7
You
cannot do the same thing with times.
Let's
say that you have the times 7:31PM in cell A1 and 8:31PM in
cell B1
the formula
:
=B1-A1
will return 0.04 if the format of the cell is either "General"
or "Number"
and it will return 1:00:00PM which is not
1 but the time representation of the serial number 0.04
Examples
of basic Excel Date and Time Formulas
Most
of the Excel functions in the category date and time are not used
alone but here are examples of what they would return.
Among
the ones that work alone are:
DATEDIF
One
note to start. If you go to the menu "Insert/Function"
you won't find this function. Excel has forgotten it. Here is how
it works. Let's
say that you have the dates 1/16/2005 in cell A1 and 1/16/2006 in
cell B1:
=DATEDIF(A1,B1,"y")
will return 1
=DATEDIF(A1,B1,"m")
will return 12
=DATEDIF(A1,B1,"d")
will return 365
TODAY
and NOW
If
you enter the formula =TODAY()
with the parentheses in a cell it will show the date at which the
spreadsheet was opened. Each time that you open the spreadsheet the
date changes.
If
you enter the formula =NOW()
with the parentheses in a cell it will show the date and time at
which the spreadsheet was opened. Each time that you open the spreadsheet the
date and time change.
The
other functions work in conjunction with others of the category or
with other functions. Here is what they return when used alone.
DAY,
MONTH, YEAR
With
a date in A1 like 12/15/2005 the formulas =DAY(A1),
=MONTH(A1)
and =YEAR(A1)
will return 15, 12 and 2005.
SECOND,
MINUTE, HOUR
With
a TIME in A1 like 1:31:45PM the formulas =SECOND(A1),
=MINUTE(A1)
and =HOUR(A1)
will return 1, 31 and 45.
WEEKDAY
If
the date in A1 is 1/16/2006 and it is a Monday the formula
=WEEKDAY(A1)
will return 2. For most users day 1 is Sunday. Check what your system
says because in some cases day 1 is Monday.
DATE, DAY, MONTH, YEAR
With the DATE function, the
arguments are always in the following order
(year,month,day) whatever the date format
specified in your regional
parameters.
With a date in cell A1;
the formula to add a day
is:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)
the formula to add a week
is
:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+7)
the formula to add a month
is:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
the formula to add a year
is:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
the last day of the month preceding
the date in A1 is:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)-DAY(A1))
the first day of the month following
the date in A1 is:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)-DAY(A1)+1)
You
can also use numbers as arguments:
=DATE(2005,12,31)
will
return 12/31/2005
TIME,
HOUR, MINUTE, SECOND
With the TIME function, the
arguments are always in the following order
(hour,minute,second).
With a time in cell A1;
the formula to add an hour
is:
=TIME(HOUR(A1),MINUTE(A1)+1,SECOND(A1))
the formula to add a minute
is
:
=TIME(HOUR(A1),MINUTE(A1)+1,SECOND(A1))
the formula to add a second
is:
=TIME(HOUR(A1),MINUTE(A1),SECOND(A1)+1)
the formula to add a day
is:
=TIME(HOUR(A1)+24,MINUTE(A1),SECOND(A1))
You
can also use numbers as arguments:
=TIME(2,31,45)
will
return 2:31:45AM
=TIME(14,31,45)
will
return 2:31:45PM
To sum hours over a total of 24, you
must modify the format of the result cell.
"Format"/"Cell"/"Personalized"/"[h]:mm"
To transform minutes in hours and
minutes (125 becoming 2:05), use this
formula (the format of the cell
where the formula resides
must be hh:mm):
=A1/1440
When you want to
multiply hours and minutes by an hourly rate to obtain a salary
with
the time in A1 and the rate in B1 you must
use the following formula in C1:
=(HOUR(A1)*B1)+(MINUTE(A1)/60*B1)
In
plain English, multiply the number of hours by the rate and then
add the number of minutes divided by 60 multiplied by the rate.
Don't forget to set the format of cell B1 to "Currency".
DATEDIF,
NOW, AND and IF
My
client wanted a spreadsheet that would tell her when it is the birthday
of an employee. We created a spreadsheet with the names in column
A and the dates of birth in column B. In cell D1 was this formula
=NOW()
that changes date each day. In column C we put this formula:
=IF(AND(DATEDIF(D1,B2,"d")=0,DATEDIF(D1,B2,"m")=0),"Anniversary","")
meaning
that if the date difference in days and in months between cell B2
and cell D1 is zero then show "Anniversary" else show
nothing.
We
fine tuned:
=IF(AND(DATEDIF(D1,B2,"d")=0,DATEDIF(D1,B2,"m")=0),"Happy
Anniversary",IF(DATEDIF(D1,B2,"d")<7,"Anniversary
coming",""))
meaning
that if the date difference in days and in months between cell B2
and cell D1 is zero then show "Happy Anniversary" else if the
date difference in days between cell B2 and cell D1 is smaller than 7 return
"Anniversary coming" else show nothing.
Copy/paste
the formula above in your spreadsheet. If you want to be alerted
more that a week before the anniversary change the 7 for 30 in the
formula. This way you will be alerted a month in advance.
|
More
on Dates
You
own coupons and you want a report showing their value
at the end of each year whatever the number of
years. Here is an application that does just that using
data and time formulas.
Open
"excel-date-2.xls"
|
|
More
on Dates
You
own coupons and you want a report showing their value
at the each anniversary whatever the number of
years. Here is an application that does just that using
data and time formulas.
Open
"excel-dates-coupons.xls"
|
|
