Adding hours and minutes..

[Avery Wagg]

I'd like to add 10,311 hours and 20 minutes (10311:20), onto a calculated
time (in minutes), but I can't figure out how to turn this large number into
something excel can add time to.

Any hints?

...Avery

 

[Ron Rosenfeld]

I'd like to add 10,311 hours and 20 minutes (10311:20), onto a calculated
time (in minutes), but I can't figure out how to turn this large number into
something excel can add time to.

Any hints?

..Avery

You have a problem.

The maximum time value that you can type into a cell is 9999:59:59. If you
type a value that is greater than or equal to 10,000 hours (10000:00:00), the
time appears as a text string.

The maximum time value that you can calculate using a formula is
71003183:59:59. This value equates to 12/31/9999 23:59:59, which is the maximum
time that you can use, at least in Excel 2002.

If you can operate within those constraints, perhaps by entering your large
times as two smaller times, and then summing them, that would be one solution.

Otherwise you will need to treat them as ordinary numbers and do the
appropriate math.

For example --

A1: your calculated time
A2: 9311:20
A3: 1000:00
A4: =SUM(A1:A3)

Format the cells as [h]:mm


--ron

 

[Peo Sjoblom]

1. You can't type 10311:20 in a cell without it becoming text, so just to
enter that value you need to use

=10311/24+"00:20"

and format as [hh]:mm will return a time value of 10311:20
then you can add time to it with a normal addition, assume you put that in
A1 and in A2 you have
00:40 (40 minutes)

=A1+A2

returns 10312:00




--
Regards,

Peo Sjoblom

(No private emails please)

 

[mangesh_yadav]

You cannot convert it to 10311:20 and expect excel toread it as time.
You would need to manipulate it. You may simply treat it as 10311.20
and keep adding time to it, check the following link

http://excelforum.com/showthread.php?t=375890&highlight=time

Mangesh

 

[Avery Wagg]

Hi Peo,

Your solution works like a charm! Thanks ever so much. I can now automate
my flight logs using Excel.

...Avery

1. You can't type 10311:20 in a cell without it becoming text, so just to
enter that value you need to use

=10311/24+"00:20"

and format as [hh]:mm will return a time value of 10311:20
then you can add time to it with a normal addition, assume you put that in
A1 and in A2 you have
00:40 (40 minutes)

=A1+A2

returns 10312:00




--
Regards,

Peo Sjoblom

(No private emails please)

I'd like to add 10,311 hours and 20 minutes (10311:20), onto a calculated
time (in minutes), but I can't figure out how to turn this large number
into something excel can add time to.

Any hints?

..Avery