Time and Date Calculations
If you want to use these real time values in other calculations, the numbers listed in Table below in mind.
Magic numbers for time and date calculations| Number | Meaning |
| 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 time-use this:
=A1*24
If a cell contains the true date and the true time (as in 22/May/07 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 will also accept m or y as the result to return-that is, Months or Years. (The DATEDIF function is undocumented in Excel 97 and is really a Lotus 123 function.)
If you do not knowin 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 PM. in cell A1, and the end time being 9:50 AM. 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.
Alternatively, you can work around this in these two ways, ensuring a positive result:
=MAX(A1:A2)-MIN(A1:A2)
or (you may need to format the cell as a time if it defaults to a numeric format):
=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. Go to the Office button → Excel Options → Add Ins, select the Analysis ToolPak, and click Go. Enable the Analysis ToolPak checkbox, and click OK (pre-2007, select Tools → Add-Ins, enable the Analysis ToolPak checkbox, and then click Yes if you are asked if you want to install it).
You can find all of these functions under the Formula tab in Functions Library → Date & Time (pre-2007, 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.
By default, this Add-In is not installed in Excel 2007. To install it, go to the Office button → Excel Options → Add Ins, select the Analysis ToolPak, and click Go. Enable the Analysis ToolPak checkbox, and click OK.
