Adding dates and times

[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

[Dave Peterson]

Just subtract the smaller from the larger and format the cell as: [hh]:mm




DejaVu wrote:
>
> 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
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=479280

--

Dave Peterson

[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" wrote:

> Just subtract the smaller from the larger and format the cell as: [hh]:mm
>
>
>
>
> DejaVu wrote:
> >
> > 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
> >
> > --
> > DejaVu
> > ------------------------------------------------------------------------
> > DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
> > View this thread: http://www.excelforum.com/showthread...hreadid=479280
>
> --
>
> Dave Peterson
>

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

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





DejaVu wrote:
>
> 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
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=479280

--

Dave Peterson