Adding dates and times

D

DejaVu

I need to figure out how to add dates and times in excel! I've been
playing around with it but can't seem to get it the way I need it.
Say, for example, I have two dates and times:
1.) 10/07/05 13:30
2.) 10/10/05 15:45
I want it to somehow return _74_hours_and_15_minutes_ in the form
*74:15*

Is this possible!?

TIA,
DejaVu
 
S

Sloth

A couple of notes.

This only works for dates after 1/0/1900 ('0' is on purpose) and before
12/31/9999.

You only need [h]:mm as the custom format.

Dates are actually stored as the number of days from 1/0/1900. You can
verify this by entering a date and then formating the cell as a number. You
can't actually enter 1/0/1900, but you can enter 0 and format it as a date to
achieve a result of 1/0/1900 12:00AM.

Dates without times are entered as 12:00AM of that day.

You can do anything to dates that you can do to a number. Just realize that
the result is expressed in days, and must be formatted appropriately.

Example:
A1: 10/07/05 13:30
A2: 10/10/05 15:45
A3: =A2-A1 (results in 3.09375. Format as [h]:mm to appear as 74:15)
A4: =(A2-A1)*60 (results in 74.25 the decimal number of hours. Format as a
number or it might appear as something like 3/14/1900 6:00 AM)

Dave Peterson said:
Just subtract the smaller from the larger and format the cell as: [hh]:mm
Click to expand...
 
D

DejaVu

Thanks Dave Peterson and Sloth -
That was exactly what I was looking for. I tried this several times
but I could not get the hours to add days. I had my cell formatted a
*hh:mm*, and it wouldnt work. I changed it to *[hh]:mm* and it worke
perfectly!

One more question: What will happen if I end up with a negative time?
Say, for example, I have this:
A1: 10/16/05 17:55
A2: 10/15/05 11:30
A3: A2 - A1 = _____?_____

Thanks again,
DejaV
 
D

Dave Peterson

You could change the base date to 1904--but that will mess up your dates.

You could try tools|options|calculation tab|check "1904 date system"--but be
prepared to toggle it back after you see what it does with your dates.

The actual subtraction works fine--but the display will show ####'s.

So one way around it is to use two cells--one for the calculated value:
=a1-a2
(say in A3)
(hide this if you want)
Then use another cell A4(?) to show the results:

=IF(A3<0,"-","")&TEXT(ABS(A3),"[hh]:mm")

This cell is text, though--so use that intermediate cell if you need to do
further calculations.




Thanks Dave Peterson and Sloth -
That was exactly what I was looking for. I tried this several times,
but I could not get the hours to add days. I had my cell formatted as
*hh:mm*, and it wouldnt work. I changed it to *[hh]:mm* and it worked
perfectly!

One more question: What will happen if I end up with a negative time?
Say, for example, I have this:
A1: 10/16/05 17:55
A2: 10/15/05 11:30
A3: A2 - A1 = _____?_____

Thanks again,
DejaVu
Click to expand...
 
Top