Microsoft Excel How TO Tips and Tricks

Share |

Using Date and Time Functions in Excel

Adding Dates

 

You can add some number of days to a date by simply using the =SUM function. Since Excel stores dates as
a number of days, no further work is required. For example, to add 5 days to 1/1/98, in A1, use =A1+5,
which gives 1/6/98.
To add a number of months or years to a date, you first need to decompose the initial date into its year, month,
and day components, add in the desired offset, and then have Excel put the components back together.
For example, say you have a date in A1, to which you want to add 3 months and 4 days. You'd use the following formula:
=DATE(YEAR(A1) , MONTH(A1)+3, DAY(A1)+4)
Excel will automatically handle the situation which arises when you pass a number greater than 12 to the MONTH function, or a number greater than 31 to the DAY function.
For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula
=DATE(YEAR(A1) ,MONTH(A1) +6, DAY(A1)+10)
gives 3/7/98.
Generally, you cannot add two dates that are in serial format. For example, adding 1/15/1998 and 6/15/1998 gives 6/30/2096, which is essentially meaningless. If you want to add some number of days to a date, but exclude weekends and holidays, you can use the WORKDAY function, which is part of the Analysis Tool Pack.
Note, however, that adding a month to a date may give you a result that you do not expect. For example, suppose A1 contains the date 31-Jan-2002. If you use the formula
=DATE(YEAR(A1) , MONTH(A1)+1, DAY(A1))
you will get the date 3-March-2002, because the "31st" day of February, 2002, is 3-March. The formula below will work around this issue, returning the last day of the next month if the date in A1 is a day that does not exist in the next month.
=DATE(YEAR(A1) ,MONTH(A1) +1,MIN(DAY( A1),DAY(DATE( YEAR(A1), MONTH(A1) +2,0))))
For example, if A1 contains the date 31-Jan-2002, this formula will return 28-Feb-2002. It is important that you and your users understand what "one month later" means in the context of your workbook.

Calculate Due Date

Let's say you have a bill due every 6 months.  To calculate the next due date from the current date in cell A1 you would use this formula. =DATE(YEAR(A1) ,MONTH(A1) +7,0 Calculate the number of days in a month Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula. If cell A1 contains a date, this formula will return the number of days in the month:    =DAY(DATE(YEAR( A1),MONTH( A1)+1,1)- 1). Computing Age Computing someone's age from their birthday is simple, using the =DATEDIF function. Suppose that a person's birthday is in A1. The following will return their age in Years, Month, and Days: =DATEDIF(A1, NOW(),"y")&" Y, "& DATEDIF(A1,NOW( ),"ym")& " M, " & DATEDIF(A1,NOW( ),"md") & " D" Convert Date Cell into 3 Columns If your date is in A1: (eg; 05/01/2005) B1: =TEXT(A1,"dd" ) C1: =TEXT(A1,"mmm" ) D1: =TEXT(A1,"yyyy" ) How can I calculate future dates in Excel? Excel can be used to quickly calculate future dates. For example: Enter a date in cell A1 (type =NOW() to use today's date); the date will be displayed according to the number format applied to the cell. In cell B1, enter =A1 + 7 to calculate the date one week later. Excel's Autofill feature can be used to display a list of dates using the same calculation ( e.g. a list of dates 7 days apart). To do this, select the cell which contains the calculation (e.g. cell B1) and drag the SMALL', OFFSETX, 16, OFFSETY, 16, BGCLASS, 'lore_glossary_ term_popup', FGCLASS, 'lore_glossary_ term_popup_ definition', TEXTFONTCLASS, 'lore_glossary_ term_popup_ definition', CAPTIONFONTCLASS, 'lore_glossary_ term_popup_ caption'); }" onmouseout=" function anonymous() { nd(); }" href="javascript: void( 0);"> small black box in the lower-right corner of the cell down or across some cells. Release the mouse button to display the list of dates.

How to Accumulate Hours and Minutes Greater Than 24 Hours

If you want to correctly display a time greater than 24 hours, you can use the 37:30:55 built-in format. If you want to use a custom format instead, you must enclose the hours parameter of the format in brackets, for example:    [h]:mm  How to Calculate Elapsed Time When you subtract the contents of one cell from another to find the amount of time elapsed between them, the result is a serial number that represents the elapsed hours, minutes, and seconds. To make this number easier to read, use the h:mm time format in the cell that contains the result. In the following example, if cells C2 and D2 contain the formula =B2-A2, and cell C2 is formatted in the General format, the cell displays a decimal number (in this case, 0.53125, the serial number representation of 12 hours and 45 minutes).    A1: Start Time   B1: End Time    C1: Difference  D1: Difference                                          (General)       (h:mm)     A2: 6:30 AM      B2: 7:15 PM     C2: 0.53125     D2: 12:45      If midnight falls between your start time and end time, you must account for the 24-hour time difference. You can do this by adding the number 1, which represents one 24-hour period. For example, you might set up the following table, which allows for time spans beyond midnight.    A1: Start Time    B1: End Time    C1: Difference  D1: Difference                                           (General)       (h:mm)     A2: 7:45 PM       B2: 10:30 AM    C2: 0.61458333  D2: 14:45      To set up this table, type the following formula in cells C2 and D2:      =B2-A2+IF(A2>B2,1)  How to Convert a Date to Its Decimal Number Equivalent To convert a serialized date (h:mm:ss) to a decimal number (0.00), you must convert the serial number to a decimal by converting to a 24-hour base. You do this by multiplying the time by 24 as follows      =Time*24   The result is 4.5. If the cell contains both a  date and a time, use the following formula:   =(Time-INT(Time) )*24 For example, if cell A1 reads "6/20/96 4:30 AM", the formula is:   =(A1-INT(A1) )*24 The result again is 4.5. How to Find the Number of Days Between Today and a Future Date To find the number of days between now and a date sometime in the future, use the following formula      ="mm/dd/yy"-NOW()  where "mm/dd/yy"var> is the future date. Use the General format to format the cell that contains the formula How to Find the Number of Days, Months, and Years Between Two Dates To calculate the number of days, months, and years between two dates, where the start and end dates are entered in cells A1 and A2 respectively, follow these steps: 1. Create a new workbook 2. Type the following data in the workbook:    A1:    03/25/94      A2:    05/01/98       3. Type the following formula in cell D1:   =YEAR(A2)-YEAR( A1)-IF(OR( MONTH(A2)   DAY(A2)   <=MONTH(A1),DAY( A2)   >=DAY(A1)),12, IF(AND(MONTH( A2)>MONTH(A1),DAY( A2)   "&A2-DATE(YEAR( A2),MONTH( A2)-IF(DAY( A2)        NOTE: If you copy and paste this formula, make sure that there are no line breaks, or the formula will not work. If you typed the formula correctly, cell D1 now displays:        4 years, 1 months, 6 days  How to Find the Number of Weekdays Between Two Dates To find the number of weekdays between two dates, where the start and end dates are typed in cells A1 and A2 respectively, follow these steps: 1. Create a new workbook. 2. Type the following data in the workbook:    A1:    03/25/94      A2:    05/01/98       3. In cell D1, type the following formula:    =NETWORKDAYS( A1,A2)       1,071 is the result. How to Increase Dates Incrementally To increase a date by a number of years, months, or days, use the formula      =DATE(YEAR(referenc e)+value1, MONTH(reference) +value2,DAY( reference) +value3)      where reference is either the date value or cell reference that contains the date, and value1, value2, and value3 are the increments by which you want to increase the year, month, and day, respectively. For example, to increase a date by one month, the formula is:    =DATE(YEAR(DATEVALU E("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,      DAY(DATEVALUE("6/20/96")))

Share |