We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??

Name In Time Out Time Total hours worked Formula
J P 8:00 AM 1:00 PM 5.00 4.5
H U 9:00 AM 2:00 PM 5.00 4.5


put this formula in D2
=(C2-INT(C2))*24-(B2-INT(B2))*24
and this in E2
=IF(D2=5,((C2-INT(C2))*24-(B2-INT(B2))*24)-0.5,(C2-INT(C2))*24-(B2-INT(B2))*24)

please advise if worked.



--
Lorenzo DÃ*az
Cad Technician


"tka8fan" wrote:

We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??

Try

=IF(((C19-B19)*24)=5,C19-(B19+TIME(0,30,0)),C19-B19)

Mike

"tka8fan" wrote:

We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30
minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we
want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried
doing
the formula as text with no luck. Any ideas from people far smarter than
I??

I'm sorry, Mr. Diaz, but I could not get your formula to work correctly,
however, the answers provided by Bob and Mike both worked. Thanks so much
for taking the time to respond!!

"ldiaz" wrote:




Name In Time Out Time Total hours worked Formula
J P 8:00 AM 1:00 PM 5.00 4.5
H U 9:00 AM 2:00 PM 5.00 4.5


put this formula in D2
=(C2-INT(C2))*24-(B2-INT(B2))*24
and this in E2
=IF(D2=5,((C2-INT(C2))*24-(B2-INT(B2))*24)-0.5,(C2-INT(C2))*24-(B2-INT(B2))*24)

please advise if worked.



--
Lorenzo DÃ*az
Cad Technician


"tka8fan" wrote:

We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??

Mike: Your formula and the one provided by Bob both worked. Thank you so
very much for taking the time to reply and help solve our problem!

"Mike H" wrote:

Try

=IF(((C19-B19)*24)=5,C19-(B19+TIME(0,30,0)),C19-B19)

Mike

"tka8fan" wrote:

We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30 minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers, I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried doing
the formula as text with no luck. Any ideas from people far smarter than I??

Mr. Phillips: Your formula and the one provided by Mike both worked. Thank
you so very much for taking the time to reply and help solve our problem!!

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30
minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we
want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried
doing
the formula as text with no luck. Any ideas from people far smarter than
I??

Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries that
cross over midnight. For example, the schedule is showing a start time of
10pm and an end time of 6am. Depending on how I format the cells containing
my formula, I either get an answer of 16 hrs or I get a cell full of #####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and the 6am
entry as "30:00" but I doubt my managers are smart enough to deal with that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30
minute
break. For example, an employee is scheduled to start at 8am and leave at
1pm. Using a simple time formula, this would give us 5 hrs, however, we
want
to automatically subtract the required 30 min break so that the schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total of
end time minus start time. If I have all the fields formatted as numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5, C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried
doing
the formula as text with no luck. Any ideas from people far smarter than
I??

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries that
cross over midnight. For example, the schedule is showing a start time of
10pm and an end time of 6am. Depending on how I format the cells
containing
my formula, I either get an answer of 16 hrs or I get a cell full of
#####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and the
6am
entry as "30:00" but I doubt my managers are smart enough to deal with
that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30
minute
break. For example, an employee is scheduled to start at 8am and leave
at
1pm. Using a simple time formula, this would give us 5 hrs, however,
we
want
to automatically subtract the required 30 min break so that the
schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total
of
end time minus start time. If I have all the fields formatted as
numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5,
C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and
tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried
doing
the formula as text with no luck. Any ideas from people far smarter
than
I??

You are a genius and my hero!! I can't make heads or tails of the formula,
but it works; and that's what really matters right now. I can't thank you
enough for your help with this project. YOUR work is going to make ME look
really good!! Just kidding. I promise to give credit where credit is due.
Thank you SO much for taking the time to respond and provide the answers I
needed. I would sure be interested in knowing how you learned all this!
Your devoted fan, Sara.

"Bob Phillips" wrote:

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries that
cross over midnight. For example, the schedule is showing a start time of
10pm and an end time of 6am. Depending on how I format the cells
containing
my formula, I either get an answer of 16 hrs or I get a cell full of
#####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and the
6am
entry as "30:00" but I doubt my managers are smart enough to deal with
that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for our
employees but also giving us a total of hours worked excluding the 30
minute
break. For example, an employee is scheduled to start at 8am and leave
at
1pm. Using a simple time formula, this would give us 5 hrs, however,
we
want
to automatically subtract the required 30 min break so that the
schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the total
of
end time minus start time. If I have all the fields formatted as
numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5,
C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and
tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even tried
doing
the formula as text with no luck. Any ideas from people far smarter
than
I??

It doesn't matter about the credit, we have all done it in our time.

One thing I don't get in your requirement is this. If the time is - 5
hours, we deduct the 30 mins break. But what about a total time of say 4:45?
That stays at 4:45 as it stands, but wouldn't this mean that a break has
been taken, maybe 30 mins, but maybe just 15 mins.

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
You are a genius and my hero!! I can't make heads or tails of the
formula,
but it works; and that's what really matters right now. I can't thank you
enough for your help with this project. YOUR work is going to make ME
look
really good!! Just kidding. I promise to give credit where credit is
due.
Thank you SO much for taking the time to respond and provide the answers I
needed. I would sure be interested in knowing how you learned all this!
Your devoted fan, Sara.

"Bob Phillips" wrote:

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries
that
cross over midnight. For example, the schedule is showing a start time
of
10pm and an end time of 6am. Depending on how I format the cells
containing
my formula, I either get an answer of 16 hrs or I get a cell full of
#####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and
the
6am
entry as "30:00" but I doubt my managers are smart enough to deal with
that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for
our
employees but also giving us a total of hours worked excluding the
30
minute
break. For example, an employee is scheduled to start at 8am and
leave
at
1pm. Using a simple time formula, this would give us 5 hrs,
however,
we
want
to automatically subtract the required 30 min break so that the
schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the
total
of
end time minus start time. If I have all the fields formatted as
numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5,
C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in
one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and
tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even
tried
doing
the formula as text with no luck. Any ideas from people far smarter
than
I??

Any employee working 5 or more hours is required to take the 30 min unpaid
meal break. The 15 min breaks they take every two hours are paid so we don't
need to adjust our actual labor hours for that time. To date, our managers
have been sort of "manually" adjusting their scheduled labor hours for the 30
min since the schedule they were using showed the total hour diff between the
in and out times and could not reflect true work hours. Now, with your help,
it does!! and they will no longer need to adjust for the breaks on the
schedules! Does that make sense?

"Bob Phillips" wrote:

It doesn't matter about the credit, we have all done it in our time.

One thing I don't get in your requirement is this. If the time is - 5
hours, we deduct the 30 mins break. But what about a total time of say 4:45?
That stays at 4:45 as it stands, but wouldn't this mean that a break has
been taken, maybe 30 mins, but maybe just 15 mins.

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
You are a genius and my hero!! I can't make heads or tails of the
formula,
but it works; and that's what really matters right now. I can't thank you
enough for your help with this project. YOUR work is going to make ME
look
really good!! Just kidding. I promise to give credit where credit is
due.
Thank you SO much for taking the time to respond and provide the answers I
needed. I would sure be interested in knowing how you learned all this!
Your devoted fan, Sara.

"Bob Phillips" wrote:

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries
that
cross over midnight. For example, the schedule is showing a start time
of
10pm and an end time of 6am. Depending on how I format the cells
containing
my formula, I either get an answer of 16 hrs or I get a cell full of
#####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and
the
6am
entry as "30:00" but I doubt my managers are smart enough to deal with
that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for
our
employees but also giving us a total of hours worked excluding the
30
minute
break. For example, an employee is scheduled to start at 8am and
leave
at
1pm. Using a simple time formula, this would give us 5 hrs,
however,
we
want
to automatically subtract the required 30 min break so that the
schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the
total
of
end time minus start time. If I have all the fields formatted as
numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5,
C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in
one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and
tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even
tried
doing
the formula as text with no luck. Any ideas from people far smarter
than
I??

Yes it does, although I still find it a bit odd that if you work 5 hours, it
reduces to 4:30, but if you work 4:45, it doesn't. But it's your business,
you know how it works <bg

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Any employee working 5 or more hours is required to take the 30 min unpaid
meal break. The 15 min breaks they take every two hours are paid so we
don't
need to adjust our actual labor hours for that time. To date, our
managers
have been sort of "manually" adjusting their scheduled labor hours for the
30
min since the schedule they were using showed the total hour diff between
the
in and out times and could not reflect true work hours. Now, with your
help,
it does!! and they will no longer need to adjust for the breaks on the
schedules! Does that make sense?

"Bob Phillips" wrote:

It doesn't matter about the credit, we have all done it in our time.

One thing I don't get in your requirement is this. If the time is - 5
hours, we deduct the 30 mins break. But what about a total time of say
4:45?
That stays at 4:45 as it stands, but wouldn't this mean that a break has
been taken, maybe 30 mins, but maybe just 15 mins.

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
You are a genius and my hero!! I can't make heads or tails of the
formula,
but it works; and that's what really matters right now. I can't thank
you
enough for your help with this project. YOUR work is going to make ME
look
really good!! Just kidding. I promise to give credit where credit is
due.
Thank you SO much for taking the time to respond and provide the
answers I
needed. I would sure be interested in knowing how you learned all
this!
Your devoted fan, Sara.

"Bob Phillips" wrote:

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Ah, Mr. Phillips, things were going so well, but now I have hit
another
snafu. The formula works perfectly until I get to schedule entries
that
cross over midnight. For example, the schedule is showing a start
time
of
10pm and an end time of 6am. Depending on how I format the cells
containing
my formula, I either get an answer of 16 hrs or I get a cell full of
#####s.
I can get the correct answer if I enter the 10pm entry as "22:00"
and
the
6am
entry as "30:00" but I doubt my managers are smart enough to deal
with
that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for
our
employees but also giving us a total of hours worked excluding
the
30
minute
break. For example, an employee is scheduled to start at 8am and
leave
at
1pm. Using a simple time formula, this would give us 5 hrs,
however,
we
want
to automatically subtract the required 30 min break so that the
schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time
is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the
total
of
end time minus start time. If I have all the fields formatted as
numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5,
C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in
one.
Column E gives me the answer of 4.5 hrs that I want. Now, how
can I
replace
the formula using time? I even put :30 in a separate column (F)
and
tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even
tried
doing
the formula as text with no luck. Any ideas from people far
smarter
than
I??

"Bob Phillips" wrote:

It doesn't matter about the credit, we have all done it in our time.

One thing I don't get in your requirement is this. If the time is - 5
hours, we deduct the 30 mins break. But what about a total time of say 4:45?
That stays at 4:45 as it stands, but wouldn't this mean that a break has
been taken, maybe 30 mins, but maybe just 15 mins.

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
You are a genius and my hero!! I can't make heads or tails of the
formula,
but it works; and that's what really matters right now. I can't thank you
enough for your help with this project. YOUR work is going to make ME
look
really good!! Just kidding. I promise to give credit where credit is
due.
Thank you SO much for taking the time to respond and provide the answers I
needed. I would sure be interested in knowing how you learned all this!
Your devoted fan, Sara.

"Bob Phillips" wrote:

Yeah, quite easily resolved

=MOD(C20-B20,1)-((MOD(C20-B20,1))*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
Ah, Mr. Phillips, things were going so well, but now I have hit another
snafu. The formula works perfectly until I get to schedule entries
that
cross over midnight. For example, the schedule is showing a start time
of
10pm and an end time of 6am. Depending on how I format the cells
containing
my formula, I either get an answer of 16 hrs or I get a cell full of
#####s.
I can get the correct answer if I enter the 10pm entry as "22:00" and
the
6am
entry as "30:00" but I doubt my managers are smart enough to deal with
that
solution. Any further wonderful suggestions you might offer? Sara

"Bob Phillips" wrote:

=C19-B19-((C19-B19)*24=5)*TIME(0,30,0)

--
__________________________________
HTH

Bob

"tka8fan" wrote in message
...
We want to create a spreadsheet to record start and end times for
our
employees but also giving us a total of hours worked excluding the
30
minute
break. For example, an employee is scheduled to start at 8am and
leave
at
1pm. Using a simple time formula, this would give us 5 hrs,
however,
we
want
to automatically subtract the required 30 min break so that the
schedule
reflects total work hours of 4.50 - or 4:30.

I need an =IF formula that would say if end time minus start time is
greater
than or equal to 5 hrs, then subtract 30 mins, otherwise give the
total
of
end time minus start time. If I have all the fields formatted as
numbers,
I
can make it work using two diff formulas:

Column B = In time
Column C = Out time
Column D = Total hours worked =+C19-B19
Column E - formua subtracting .5 hrs =IF(C19-B19=5,
C19-B19-0.5,D19)

It was easier using two formulas than attempting to do it all in
one.
Column E gives me the answer of 4.5 hrs that I want. Now, how can I
replace
the formula using time? I even put :30 in a separate column (F) and
tried

=IF(C3-B3=5, C3-B3-F3, C3-B3)

but that doesn't get me the correct answer either. I have even
tried
doing
the formula as text with no luck. Any ideas from people far smarter
than
I??











How do I get the formula to deduct one hour on some and none on others?