Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default 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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I test values in 2 different columns in a "sumif" formula? SteveS Excel Discussion (Misc queries) 8 June 3rd 07 11:27 PM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
conditional formula to show "open" or "closed" SBS Excel Worksheet Functions 6 January 28th 06 02:48 AM
Changing "returned" values from "0" to "blank" LATATC Excel Worksheet Functions 2 October 20th 05 04:41 PM


All times are GMT +1. The time now is 07:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"