Adding time

K

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?
 
A

Ardus Petus

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

HTH
 
B

Bruno Campanini

Ken Davie said:
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?
Click to expand...

A1 = Start Time
A2 = End Time

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

Bruno
 
S

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

[email protected]
[email protected] with @tiscali.co.uk
 
K

Ken Davie

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

Ken.....
 
D

David Biddulph

Ken Davie said:
That formula works great, but now what function do i use to multiply the
total time by an hourly rate?
Click to expand...

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

Bruno Campanini

Sandy Mann said:
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
Click to expand...

Very interesting formulas, Sandy!

Thank you very much
Bruno
 
B

Bruno Campanini

Sandy Mann said:
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
Click to expand...

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
 
S

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
Click to expand...


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

[email protected]
[email protected] with @tiscali.co.uk
 
B

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<>"")
Click to expand...

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

Ciao
Bruno
 
Top