Prev: HOW CAN i DEVELOP A FORMULA THAT RESTRICY SYMBOLS CHARACTERS TO B
Next: Record time by formula
From: Jacob Skaria on 30 Apr 2010 00:26
Try the below formula and format the cell to [h]:mm

=IF(MOD(B1,1)-MOD(A1,1)>"06:00",MOD(B1,1)-MOD(A1,1)+(0.5/24),
MOD(B1,1)-MOD(A1,1))

--
Jacob (MVP - Excel)


"Chris" wrote:

>
> -- Thanks Jacob, but let me explain my dilema further. I have 6:30am in cell
> A1 & 3:00pm in cell B1; I have both of these cells formatted as custom h:mm
> AM/PM & I have C1 formatted as h:mm (in this cell is the total hours from A &
> B) I have been able to add or subtract a 30 min lunch w/formulas; my question
> is: can I have a formula in all affected cells(the total daily hours across a
> 7 day work schedule) that would add a 30 min period only to those cells that
> are greater than 6 hours?
> Chris
>
>
> "Jacob Skaria" wrote:
>
> > Hi Chris
> >
> > With start time in cell A1 and end time in cell B1
> >
> > In cell C1 apply the below formula and format the formula cell to custom
> > format [h]:mm
> >
> > =(B1-A1)+(0.5/24)
> >
> > --
> > Jacob (MVP - Excel)
> >
> >
> > "Chris" wrote:
> >
> > > adding time (30 minute lunch) to a time value (>6 hours) that is calculated
> > > from two cells that contain starting time & ending time EX 6:30am-3:00pm
> > > --
> > > Chris
First  |  Prev  | 
Pages: 1 2
Prev: HOW CAN i DEVELOP A FORMULA THAT RESTRICY SYMBOLS CHARACTERS TO B
Next: Record time by formula