How to put "between values" (e.g. 3-9) to the formula?
 

I want Excel to calculate how many hours there is between two times, but only
if there is more than 8 hours AND less than 10 hours between those two
values. How do I build up the formula? Or how to I put two different formulas
to the same cell?

How to put "between values" (e.g. 3-9) to the formula?
 

Hi,

Dies this work, it must have the later time in B1

=IF(AND((B1-A1)*24=8,(B1-A1)*24<=10),(B1-A1)*24,"Unspecified")

Because you don't say what you want for <8 10 the formula returns
unspecified.

Mike

"jounardo" wrote:

I want Excel to calculate how many hours there is between two times, but only
if there is more than 8 hours AND less than 10 hours between those two
values. How do I build up the formula? Or how to I put two different formulas
to the same cell?

How to put "between values" (e.g. 3-9) to the formula?
 

Hey, thank you, this solved part of my problem.

Now I have a follow-up question. Let's say I have worked 11 hours, and from
8-10 hours I get 4 ‚¬'s more money. This is why I wanted the 8-10 hours. But
now I figured out, that when I work more than 10 hours, the formula returns 0
hours, nevertheless I would like it to return 2 hours. Now I need to somehow
tell to the formula that if I work more than 10 hours, I still want
(B1-A1)*24 to be from 0 to 2 hours?

And how do I make a formula where I tell Excel to calculate the hours
between 21.00-07.00 if i worked 4 hours or more between those times?

-j

"Mike H" wrote:

Hi,

Dies this work, it must have the later time in B1

=IF(AND((B1-A1)*24=8,(B1-A1)*24<=10),(B1-A1)*24,"Unspecified")

Because you don't say what you want for <8 10 the formula returns
unspecified.

Mike

"jounardo" wrote:

I want Excel to calculate how many hours there is between two times, but only
if there is more than 8 hours AND less than 10 hours between those two
values. How do I build up the formula? Or how to I put two different formulas
to the same cell?

How to put "between values" (e.g. 3-9) to the formula?
 

Assuming that after 10 hours you get 6 ?'s (per hour) then try:

=MAX(0,MIN((B1-A1)*24-8,2))*4+MAX((B1-A1)*24-10,0)*6

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"jounardo" wrote in message
...
Hey, thank you, this solved part of my problem.

Now I have a follow-up question. Let's say I have worked 11 hours, and
from
8-10 hours I get 4 ?'s more money. This is why I wanted the 8-10 hours.
But
now I figured out, that when I work more than 10 hours, the formula
returns 0
hours, nevertheless I would like it to return 2 hours. Now I need to
somehow
tell to the formula that if I work more than 10 hours, I still want
(B1-A1)*24 to be from 0 to 2 hours?

And how do I make a formula where I tell Excel to calculate the hours
between 21.00-07.00 if i worked 4 hours or more between those times?

-j

"Mike H" wrote:

Hi,

Dies this work, it must have the later time in B1

=IF(AND((B1-A1)*24=8,(B1-A1)*24<=10),(B1-A1)*24,"Unspecified")

Because you don't say what you want for <8 10 the formula returns
unspecified.

Mike

"jounardo" wrote:

I want Excel to calculate how many hours there is between two times,
but only
if there is more than 8 hours AND less than 10 hours between those two
values. How do I build up the formula? Or how to I put two different
formulas
to the same cell?