Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
It's been a long time since I did any subtractions of dates, and would
much appreciate a little help please. In particular, what is the simplest way of finding the period that's elapsed bewteen two date/times? Of course, I would want to display it in the most common sense format, across a wide range. Must I convert each date first, subtract and then convert back, or can it be done in one formula? Is there ever any practical application for *adding* two date/time values? -- Terry, East Grinstead, UK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
Hi,
Dates in Excel are numbers. Today for example (31/12/2008) is 39813 and these are then formatted to display in whatever way a user wants to see them. So to your first question the difference between 2 dates is simply subtraction Later date - earlier date and format as general There are inbuilt date functions including the undocumented datedif function, have a look here. You don't have to 'convert' dates to do date arithmatic. http://www.cpearson.com/excel/datedif.aspx. With regard to adding dates I'm sure someone must want to do it but I'm at a bit of a loss to understand why anyone would want to. In practical terms it's more useful to add number of days to a date and this again uses +. Excel also has some other inbuilt date functions such as networkdays, and if you search 'Dates' in Excel help you'll find lots. Mike "Terry Pinnell" wrote: It's been a long time since I did any subtractions of dates, and would much appreciate a little help please. In particular, what is the simplest way of finding the period that's elapsed bewteen two date/times? Of course, I would want to display it in the most common sense format, across a wide range. Must I convert each date first, subtract and then convert back, or can it be done in one formula? Is there ever any practical application for *adding* two date/time values? -- Terry, East Grinstead, UK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
=INT(A2-A1) would give you the number of whole days between two date/ time
values (format as number or general). =MOD(A2-A1,1) would give you the remaining time (format as hh:mm or to suit). -- David Biddulph "Terry Pinnell" wrote in message ... It's been a long time since I did any subtractions of dates, and would much appreciate a little help please. In particular, what is the simplest way of finding the period that's elapsed bewteen two date/times? Of course, I would want to display it in the most common sense format, across a wide range. Must I convert each date first, subtract and then convert back, or can it be done in one formula? Is there ever any practical application for *adding* two date/time values? -- Terry, East Grinstead, UK |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
<With regard to adding dates I'm sure someone must want to do it but I'm at
a bit of a loss to understand why anyone would want to One application might be converting from one calendar to another. Date in calendar 1 plus startdate of calendar 1 expressed as date in calendar 2 gives date in calendar 2. -- Kind regards, Niek Otten Microsoft MVP - Excel "Mike H" wrote in message ... Hi, Dates in Excel are numbers. Today for example (31/12/2008) is 39813 and these are then formatted to display in whatever way a user wants to see them. So to your first question the difference between 2 dates is simply subtraction Later date - earlier date and format as general There are inbuilt date functions including the undocumented datedif function, have a look here. You don't have to 'convert' dates to do date arithmatic. http://www.cpearson.com/excel/datedif.aspx. With regard to adding dates I'm sure someone must want to do it but I'm at a bit of a loss to understand why anyone would want to. In practical terms it's more useful to add number of days to a date and this again uses +. Excel also has some other inbuilt date functions such as networkdays, and if you search 'Dates' in Excel help you'll find lots. Mike "Terry Pinnell" wrote: It's been a long time since I did any subtractions of dates, and would much appreciate a little help please. In particular, what is the simplest way of finding the period that's elapsed bewteen two date/times? Of course, I would want to display it in the most common sense format, across a wide range. Must I convert each date first, subtract and then convert back, or can it be done in one formula? Is there ever any practical application for *adding* two date/time values? -- Terry, East Grinstead, UK |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
"David Biddulph" <groups [at] biddulph.org.uk wrote:
=INT(A2-A1) would give you the number of whole days between two date/ time values (format as number or general). =MOD(A2-A1,1) would give you the remaining time (format as hh:mm or to suit). Mike, Niek, David: Thanks all, very helpful and got me over my mental block. So am I right from what I've read that it would be unrealistic to expect a single formula to let me enter two Date+Time values and get their difference expressed in Days:Hours:Mins:Secs, or Weeks:Days:Hours:Mins:Sec? -- Terry, East Grinstead, UK |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
Hi,
You can do that easilly. earlier date/time in A1 later day/time in A2 =A2-A1 Format as d hh:mm Mike "Terry Pinnell" wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote: =INT(A2-A1) would give you the number of whole days between two date/ time values (format as number or general). =MOD(A2-A1,1) would give you the remaining time (format as hh:mm or to suit). Mike, Niek, David: Thanks all, very helpful and got me over my mental block. So am I right from what I've read that it would be unrealistic to expect a single formula to let me enter two Date+Time values and get their difference expressed in Days:Hours:Mins:Secs, or Weeks:Days:Hours:Mins:Sec? -- Terry, East Grinstead, UK |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date arithmetic
But what happens to that when you get beyond 31 days, Mike?
-- David Biddulph "Mike H" wrote in message ... Hi, You can do that easilly. earlier date/time in A1 later day/time in A2 =A2-A1 Format as d hh:mm Mike "Terry Pinnell" wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote: =INT(A2-A1) would give you the number of whole days between two date/ time values (format as number or general). =MOD(A2-A1,1) would give you the remaining time (format as hh:mm or to suit). Mike, Niek, David: Thanks all, very helpful and got me over my mental block. So am I right from what I've read that it would be unrealistic to expect a single formula to let me enter two Date+Time values and get their difference expressed in Days:Hours:Mins:Secs, or Weeks:Days:Hours:Mins:Sec? -- Terry, East Grinstead, UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
About arithmetic Formulas | Excel Discussion (Misc queries) | |||
Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Entering arithmetic operation | New Users to Excel | |||
Simple arithmetic | Excel Discussion (Misc queries) | |||
Simple arithmetic - take 2 | Excel Discussion (Misc queries) |