If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating Dates
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 |
#2
|
|||
|
|||
Calculating Dates
"Kerrick Sawyers" wrote in message
... 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 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. |
#3
|
|||
|
|||
Calculating Dates
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 Sawyers" wrote in message ... 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 |
#4
|
|||
|
|||
Calculating Dates
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 (?) -----Original Message----- 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 Sawyers" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|