Date arithmetic

[Terry Pinnell]

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?

 

[Mike H]

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

 

[Niek Otten]

<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.

 

[Terry Pinnell]

=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
Weeksays:Hours:Mins:Sec?

 

[Mike H]

Hi,

You can do that easilly.

earlier date/time in A1
later day/time in A2

=A2-A1

Format as d hh:mm

Mike