Adding time

[Ken Davie]

If i have a start time of 5:00am and a finishing time of 4:25pm, what
function do i use to get the total time of 11 hours & 25 minutes?

 

[Ardus Petus]

Say you have start time in A1, end time in B1
=B1-A1
will give elapsed time

HTH

 

[Bruno Campanini]

If i have a start time of 5:00am and a finishing time of 4:25pm, what
function do i use to get the total time of 11 hours & 25 minutes?

A1 = Start Time
A2 = End Time

=(A2<A1) * (24 - A1 + A2) + (A2 >= A1) * (A2 - A1)

Bruno

 

[Sandy Mann]

Bruno,

Without meaning to be critical, your formula *appears* to return the correct
answer when you have, for example, 23:30 in A1 and 01:30 in A1 you get 02:00

However, if you format the cell as General you will see that the actual
value held in the cell is 23.083333333 which is 2 am on January 23 1900.
The reason is the use of 24 in your formula. Excel is taking it as 24 days
*not* 24 hours. The answer is to replace the 24 with 1 (day) as in:

=(A2<A1) * (1- A1 + A2) + (A2 >= A1) * (A2 - A1)

Shorter ways of doing the same thing:

=A2-A1+(A2<A1)

or

=MOD(A2-A1,1)

Neither of which are my original formulas
--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Ken Davie]

That formula works great, but now what function do i use to multiply the
total time by an hourly rate?

Ken.....

 

[David Biddulph]

That formula works great, but now what function do i use to multiply the
total time by an hourly rate?

It is left as an exercise for the interested reader to work out the number
of hours from the number of days.

 

[Chip Pearson]

=time*24*rate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bruno Campanini]

Bruno,

Without meaning to be critical, your formula *appears* to return the
correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you
get 02:00

However, if you format the cell as General you will see that the actual
value held in the cell is 23.083333333 which is 2 am on January 23 1900.
The reason is the use of 24 in your formula. Excel is taking it as 24
days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in:

=(A2<A1) * (1- A1 + A2) + (A2 >= A1) * (A2 - A1)

Shorter ways of doing the same thing:

=A2-A1+(A2<A1)

or

=MOD(A2-A1,1)

Neither of which are my original formulas

Very interesting formulas, Sandy!

Thank you very much
Bruno

 

[Bruno Campanini]

Bruno,

Without meaning to be critical, your formula *appears* to return the
correct answer when you have, for example, 23:30 in A1 and 01:30 in A1 you
get 02:00

However, if you format the cell as General you will see that the actual
value held in the cell is 23.083333333 which is 2 am on January 23 1900.
The reason is the use of 24 in your formula. Excel is taking it as 24
days *not* 24 hours. The answer is to replace the 24 with 1 (day) as in:

=(A2<A1) * (1- A1 + A2) + (A2 >= A1) * (A2 - A1)

Shorter ways of doing the same thing:

=A2-A1+(A2<A1)

or

=MOD(A2-A1,1)

Neither of which are my original formulas
--
HTH

Sandy
In Perth, the ancient capital of Scotland

Sandy, a friend of mine - Franz Verga - modified =A2-A1+(A2<A1) in
=A2-A1+(A2<=A1).
As a conseguence whenever you have A1=A2 the formula
correctly thinks A2 pertaining to the next day and displays
24:00 instead of Null.
Provided you have the cell Custom formatted as [h].mm

Bruno

 

[Sandy Mann]

Sandy, a friend of mine - Franz Verga - modified =A2-A1+(A2<A1) in
=A2-A1+(A2<=A1).
As a conseguence whenever you have A1=A2 the formula
correctly thinks A2 pertaining to the next day and displays
24:00 instead of Null.
Provided you have the cell Custom formatted as [h].mm

Thanks for sharing that Bruno,

It also returns 24:00 if both cells are blank, if that is a problem then you
could add another test:

=A2-A1+(A2<=A1)*(A1<>"")

--
Regards,


Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bruno Campanini]

Thanks for sharing that Bruno,

It also returns 24:00 if both cells are blank, if that is a problem then
you could add another test:

=A2-A1+(A2<=A1)*(A1<>"")

Or
=(A2-A1+(A2<=A1))*AND(A1<>"",A2<>"")

Ciao
Bruno