set a minimum value for a cell
 

I have a sheet with two times, and a formula to figure out how much to
charge, depending on the difference between those two times. I would like to
set a minimum of 4 hours, such that no matter what two times are entered, if
they are less than four hours apart, the result will be 4 hours, but if they
are anything more than 4 hours apart, the result will be the actual
difference.

set a minimum value for a cell
 

Try something like this:

A1 = start
B1 = end

=IF(COUNT(A1:B1)=2,MAX(4,B1-A1),"")

--
Biff
Microsoft Excel MVP


"Nevets" wrote in message
...
I have a sheet with two times, and a formula to figure out how much to
charge, depending on the difference between those two times. I would like
to
set a minimum of 4 hours, such that no matter what two times are entered,
if
they are less than four hours apart, the result will be 4 hours, but if
they
are anything more than 4 hours apart, the result will be the actual
difference.

set a minimum value for a cell
 

hi
post your formula please.

Regards
FSt1

"Nevets" wrote:

I have a sheet with two times, and a formula to figure out how much to
charge, depending on the difference between those two times. I would like to
set a minimum of 4 hours, such that no matter what two times are entered, if
they are less than four hours apart, the result will be 4 hours, but if they
are anything more than 4 hours apart, the result will be the actual
difference.

set a minimum value for a cell
 

a1= start time 08:00
b1 = end time 12:00
d1 = 4:00
all formatted as [hh]:mm

formula
=MAX(B1-A1,$D$1)

will return the maximum of the time difference and 4 hrs. Its best to keep
the 4hr constant in a cell in case you want to change it in which case you
only alter a single cell.

Mike

"Nevets" wrote:

I have a sheet with two times, and a formula to figure out how much to
charge, depending on the difference between those two times. I would like to
set a minimum of 4 hours, such that no matter what two times are entered, if
they are less than four hours apart, the result will be 4 hours, but if they
are anything more than 4 hours apart, the result will be the actual
difference.

set a minimum value for a cell
 

Assuming C13 is the "on-site" time, and D13 is the "off-site" time, and
E13 is a cell whose formula is =D13-C13, and
F13 is a cell with prep/travel time I enter depending on the location of the
event.
My formula in the cell used to figure out how many hours to charge is:
=F13+(E13-INT(E13))*24
What I need is something that will put a minimum of 4 hrs in the E13 cell,
but it needs to be formatted in the same way as the other values in that cell
(i.e. 4 hrs actually will equal 0.16667, since that value x 24 hrs = 4 hrs)

"FSt1" wrote:

hi
post your formula please.

Regards
FSt1

"Nevets" wrote:

I have a sheet with two times, and a formula to figure out how much to
charge, depending on the difference between those two times. I would like to
set a minimum of 4 hours, such that no matter what two times are entered, if
they are less than four hours apart, the result will be 4 hours, but if they
are anything more than 4 hours apart, the result will be the actual
difference.

set a minimum value for a cell
 

Take your pick:

=IF(COUNT(C13:D13)=2,MAX(4/24,D13-C13),0)

=IF(COUNT(C13:D13)=2,MAX(TIME(4,,),D13-C13),0)

--
Biff
Microsoft Excel MVP


"Nevets" wrote in message
...
Assuming C13 is the "on-site" time, and D13 is the "off-site" time, and
E13 is a cell whose formula is =D13-C13, and
F13 is a cell with prep/travel time I enter depending on the location of
the
event.
My formula in the cell used to figure out how many hours to charge is:
=F13+(E13-INT(E13))*24
What I need is something that will put a minimum of 4 hrs in the E13 cell,
but it needs to be formatted in the same way as the other values in that
cell
(i.e. 4 hrs actually will equal 0.16667, since that value x 24 hrs = 4
hrs)

"FSt1" wrote:

hi
post your formula please.

Regards
FSt1

"Nevets" wrote:

I have a sheet with two times, and a formula to figure out how much to
charge, depending on the difference between those two times. I would
like to
set a minimum of 4 hours, such that no matter what two times are
entered, if
they are less than four hours apart, the result will be 4 hours, but if
they
are anything more than 4 hours apart, the result will be the actual
difference.