Calculating Dates

  • Thread starter Thread starter Kerrick Sawyers
  • Start date Start date
K

Kerrick Sawyers

Need a little help:

Trying to calculate the number of days between two dates
formatted as mm/dd/yy hh:mm:ss. Any help would be greatly
appreciated. Thanks!

Kindest regards,
Kerrick
 
Kerrick Sawyers said:
Need a little help:

Trying to calculate the number of days between two dates
formatted as mm/dd/yy hh:mm:ss. Any help would be greatly
appreciated. Thanks!

Kindest regards,
Kerrick
Click to expand...

For date pure and simple (not including times), just subtract the earlier
date from the later, and format the result as a number (or general). That's
the number of days difference.

Dates in Excel are stored as numbers of days since 31st Dec 1899 (so 1st Jan
1900 is 1). Today (7th Oct 2003) is 37901, as you can see by putting
=TODAY() in a cell and formatting as a number.

Times are just fractions of a day. So 6am today would be 37901.25.

Hence, when you subtract dates/times, such as midday yesterday (37900.5)
from 6am today (37901.25), your result will be 0.75. If necessary, that is
if you just want a whole number of days, you may want to consider truncating
or rounding as appropriate.
 
Kerrick,

Dates are stored as a serial number, so just subtract the smaller from the
larger and format as General.

If you want to ignore weekends, you need the NETWORKDAYS function which is
part of the Analysis Toolpak add-in, and can omnit holidays as well.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Kerrick,

this is what happens what I subtract one date from another:
These are delivery dates.

Report 1 Report 2 Diff in Days
7/25/2003 9/25/2003 62
12/15/2003 37970 (?)
12/15/2003 12/15/2003 0
6/28/2003 -37800 (?)
9/15/2003 9/5/2003 -10
12/15/2003 37970 (?)
 
Back
Top