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

[jounardo]

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?

 

[Mike H]

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]

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

 

[Sandy Mann]

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

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