Adding time
 

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?

Adding time
 

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

HTH
--
AP

"Ken Davie" a écrit dans le message de news:
...
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?

Adding time
 

"Ken Davie" wrote in message
u...
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

Adding time
 

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


with @tiscali.co.uk


"Bruno Campanini" wrote in message
...
"Ken Davie" wrote in message
u...
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

Adding time
 

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

Ken.....

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Bruno Campanini" wrote in message
...
"Ken Davie" wrote in message
u...
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

Adding time
 

"Ken Davie" wrote in message
u...
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.
--
David Biddulph

Adding time
 

=time*24*rate


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

"Ken Davie" wrote in message
u...
That formula works great, but now what function do i use to
multiply the
total time by an hourly rate?

Ken.....

"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Bruno Campanini" wrote in
message
...
"Ken Davie" wrote in message
u...
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

Adding time
 

"Sandy Mann" wrote in message
...
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

Adding time
 

"Sandy Mann" wrote in message
...
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

Adding time
 

"Bruno Campanini" wrote in message
...

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


with @tiscali.co.uk

Adding time
 

"Sandy Mann" wrote in message
...

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