adding time in a work schedule

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

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
 
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)
 
-- 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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
-- 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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top