Hack 38 Hack Excel s Date and Time Features

   

Hack 38 Hack Excel's Date and Time Features

Excel's date and time feature is great if you're creating simple spreadsheets, but they can cause problems for more advanced projects. Fortunately, there are ways to get around Excel's assumptions when they don't meet your needs .

Excel (by default) uses the 1900 date system. This means the date 1 Jan 1900 has an underlying numeric value of 1, 2 Jan 1900 has a value of 2, and so forth. These values are called serial values in Excel, and they enable you to use dates in calculations.

Times are very similar, but Excel treats times as decimal fractions, with 1 being the time 24:00 or 00:00. 18:00 has a numeric value of 0.75 because it is three- quarters of 24 hours.

To see the numeric value of a date and/or a time, format the cell containing the value as General . For example, the date and time 3/July/2002 3:00:00 PM has a numeric value of 37440.625, with the number after the decimal representing the time, and the 37440 representing the serial value for 3/July/2002.

Adding Beyond 24 Hours

You can add times by using the SUM function (or a simple plus sign). Therefore, =SUM(A1:A5) would result in Total Hours if A1:A5 contained valid times. There is, however, a big "Gotcha!" Unless told otherwise , Excel will not add past 24 hours. This is because when a time value exceeds 24 hours (a true value of 1), it rolls into a new day and starts again. To force Excel not to default back to a new day after 24 hours, you can use a cell format of 37:30:55 or a custom format of [h]:mm:ss.

You can use a similar format to get the total minutes or seconds of a time.To get the total minutes of the time 24:00, for instance, format the cell as [m] and you will get 1440. To get the total seconds, use a custom format of [s] and you get 86400.

Time and Date Calculations

If you want to use these real time values in other calculations, keep the following "magic" numbers in mind:

60

60 minutes or 60 seconds

3600

60 secs * 60 mins

24

24 hours

1440

60 mins * 24 hours

86400

24 hours * 60 mins * 60 secs

Once you are armed with these magic numbers and the preceding information, you'll find it's much easier to manipulate times and dates. Take a look at the following examples to see what we mean (assume the time is in cell A1).

If you have the number 5.50 and you really want 5:30 or 5:30 a.m., use this:

 =A1/24 

and format as needed.

If it should be 17:30 or 5:30 p.m., use this:

 =(A1/24)+0.5 

To achieve the opposite that is, a decimal time from a true timeuse this:

 =A1*24 

If a cell contains the true date and the true time (as in 22/Jan/03 15:36) and you want only the date, use this:

 =INT(A1) 

To get only the time, use this:

 =A1-INT(A1) 

or:

 =MOD(A1,1) 

and format as needed.

To find out the difference between two dates, use this:

 =DATEDIF(A1,A2,"d") 

where A1 is the earlier date.

This will produce the number of days between two dates. It also will accept " m " or " y " as the result to returnthat is, Months or Years . (The DATEDIF function is undocumented in Excel 97 and is really a Lotus 123 function.)

If you do not know in advance which date or time is the earliest, the MIN and MAX functions can help. For example, to be assured of a meaningful result, you can use this:

 =DATEDIF(MIN(A1,A2),MAX(A1,A2),"d") 

Also, when working with times, you might need to account for start time and end time, with the start time being 8:50 p.m. in cell A1, and the end time being 9:50 a.m. in cell A2. If you subtract the start time from the end time (=A2-A1) , you get ###### , as Excel, by default, cannot work with negative times. See [Hack #74] for more on how to work with negative times.

Alternatively, you can work around this in these two ways, ensuring a positive result:

 =MAX(A1,A2)-MIN(A1:A2) 

or:

 =A1-A2+IF(A1>A2,1) 

You can also tell Excel to add any number of days, months, or years to any date:

 =DATE(YEAR(A1)+value1,MONTH(A1)+value2,DAY(A1)+value3) 

To add one month to a date in cell A1, use this:

 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) 

Excel also offers some additional functions that are part of the Analysis ToolPak. Click Add-Ins on the Tools menu. Click to select the Analysis ToolPak checkbox, and then click Yes if you are asked if you want to install it. Additional functions, such as EDATE , EOMONTH , NETWORKDAYS , and WEEKNUM , will be available to you.

You can find all of these functions under the Date & Time category of the Paste Function dialog in the Function Wizard. The functions are easy to use; the hard part is knowing they're available and turning them on.

Real Dates and Times

Sometimes spreadsheets with imported data (or data that was entered incorrectly) end up with dates and times being seen as text and not real numbers. You can spot this easily in Excel by widening the columns a bit, selecting a column, selecting Format Cells Alignment, and then changing the Horizontal alignment to General (the default format for cells). Click OK and examine your dates and times closely. If any are not right-aligned, Excel doesn't think they are dates.

To fix this, first copy any empty cell, and then select the column and format as any Date and/or Time format. While the column is still selected, select Edit Paste Special Value Add. This will force Excel to convert any text dates and times to real dates and times. You might need to change the format again. Another simple method is to reference the cell(s) like this:

 =A1+0 or A1*1 

A Date Bug?

Excel incorrectly assumes that the year 1900 was a leap year. This means Excel's internal date system believes there was a 29 Feb 1900, when there wasn't! The most surprising part is that Microsoft did this intentionally, or so they say! More information is available at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q181370.

Here are some additional good links to information on dates and times:

HOW TO: Use Dates and Times in Excel 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214094#6

Text or Number Converted to Unintended Number Format

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214233

Maximum Times in Microsoft Excel

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214386

Dates and Times Displayed as Serial Numbers When Viewing Formulas

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241072

Controlling and Understanding Settings in the Format Cells Dialog Box

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264372

How to Use Dates and Times in Microsoft Excel

http://support.microsoft.com/default.aspx?scid=kb;en-us;214094

Dates and times are probably one of the most confusing areas within Excel. Armed with this information, hopefully you will understand more about their many quirks and have an easier time dealing with them.