Time formula
 

I am trying to prepare an employee time off log for 20 employees. I have read
through the discussions, but can't seem to find what I need. Any help would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am to 5pm.
When someone takes off a full day, only 8 hours needs to be charged against
him, instead of 9. I need a formula that will display only 8 hours used
instead of 9 when I subtract the two time cells of one containing 8:00, the
other containing 17:00. I also need this formula to calculate half days, such
as when an employee leaves maybe from 10 to 2, for example. Everything I have
tried displays either 0.00, FALSE, or the correct number appears but then it
messes up my other formula for "time allowed minus time used equals time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking off a
full day) and d14 is the time returning (or 5:00, end of the day) and e14
being the difference between the two cells.

Time formula
 

It's a lot easier when you show the formulas you have tried. Otherwise, we
are just guessing. As a guess, try the following:

1. For the full day, limit the result to 8 hours by using the Min function,
as in: =min(d14-c14,time(8,0,0))
2. For the half day, the formula is the same (to get a difference in time,
it's just subtraction: =d14-c14). If you are getting bad results, it's
likely the data that's wrong. You state your times for a full day are 8:00
to 17:00, yet for a half day is 10 to 2. Is the solution as simple as
entering consistent times -- ie, 10:00 to 14:00?

Regards,
Fred.

"Jessie" wrote in message
...
I am trying to prepare an employee time off log for 20 employees. I have
read
through the discussions, but can't seem to find what I need. Any help
would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am to
5pm.
When someone takes off a full day, only 8 hours needs to be charged
against
him, instead of 9. I need a formula that will display only 8 hours used
instead of 9 when I subtract the two time cells of one containing 8:00,
the
other containing 17:00. I also need this formula to calculate half days,
such
as when an employee leaves maybe from 10 to 2, for example. Everything I
have
tried displays either 0.00, FALSE, or the correct number appears but then
it
messes up my other formula for "time allowed minus time used equals time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking off a
full day) and d14 is the time returning (or 5:00, end of the day) and e14
being the difference between the two cells.

Time formula
 

I tried the min formula you gave and it works just as I needed. Thank you. I
have been using the military time consistently with my project, I suppose I
just entered 10 to 2 for simplicity sake. As far as I can tell, using
military time seems to work well with what I have set up and with that
awesome formula you gave me, but does it make a difference to excel whether I
use military time or standard?

"Fred Smith" wrote:

It's a lot easier when you show the formulas you have tried. Otherwise, we
are just guessing. As a guess, try the following:

1. For the full day, limit the result to 8 hours by using the Min function,
as in: =min(d14-c14,time(8,0,0))
2. For the half day, the formula is the same (to get a difference in time,
it's just subtraction: =d14-c14). If you are getting bad results, it's
likely the data that's wrong. You state your times for a full day are 8:00
to 17:00, yet for a half day is 10 to 2. Is the solution as simple as
entering consistent times -- ie, 10:00 to 14:00?

Regards,
Fred.

"Jessie" wrote in message
...
I am trying to prepare an employee time off log for 20 employees. I have
read
through the discussions, but can't seem to find what I need. Any help
would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am to
5pm.
When someone takes off a full day, only 8 hours needs to be charged
against
him, instead of 9. I need a formula that will display only 8 hours used
instead of 9 when I subtract the two time cells of one containing 8:00,
the
other containing 17:00. I also need this formula to calculate half days,
such
as when an employee leaves maybe from 10 to 2, for example. Everything I
have
tried displays either 0.00, FALSE, or the correct number appears but then
it
messes up my other formula for "time allowed minus time used equals time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking off a
full day) and d14 is the time returning (or 5:00, end of the day) and e14
being the difference between the two cells.

Time formula
 

The format of a cell (other than Text) has no effect on Excel functions. You
can format the time any way you want and the formulas will still work.

Regards,
Fred.

"Jessie" wrote in message
...
I tried the min formula you gave and it works just as I needed. Thank you.
I
have been using the military time consistently with my project, I suppose
I
just entered 10 to 2 for simplicity sake. As far as I can tell, using
military time seems to work well with what I have set up and with that
awesome formula you gave me, but does it make a difference to excel
whether I
use military time or standard?

"Fred Smith" wrote:

It's a lot easier when you show the formulas you have tried. Otherwise,
we
are just guessing. As a guess, try the following:

1. For the full day, limit the result to 8 hours by using the Min
function,
as in: =min(d14-c14,time(8,0,0))
2. For the half day, the formula is the same (to get a difference in
time,
it's just subtraction: =d14-c14). If you are getting bad results, it's
likely the data that's wrong. You state your times for a full day are
8:00
to 17:00, yet for a half day is 10 to 2. Is the solution as simple as
entering consistent times -- ie, 10:00 to 14:00?

Regards,
Fred.

"Jessie" wrote in message
...
I am trying to prepare an employee time off log for 20 employees. I have
read
through the discussions, but can't seem to find what I need. Any help
would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am to
5pm.
When someone takes off a full day, only 8 hours needs to be charged
against
him, instead of 9. I need a formula that will display only 8 hours used
instead of 9 when I subtract the two time cells of one containing 8:00,
the
other containing 17:00. I also need this formula to calculate half
days,
such
as when an employee leaves maybe from 10 to 2, for example. Everything
I
have
tried displays either 0.00, FALSE, or the correct number appears but
then
it
messes up my other formula for "time allowed minus time used equals
time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking
off a
full day) and d14 is the time returning (or 5:00, end of the day) and
e14
being the difference between the two cells.

Time formula
 

The min formula works very well for determining the differnce between the two
times for one day, but it does not sum the total over serveral days at the
bottom of the sheet. Any ideas?

"Fred Smith" wrote:

The format of a cell (other than Text) has no effect on Excel functions. You
can format the time any way you want and the formulas will still work.

Regards,
Fred.

"Jessie" wrote in message
...
I tried the min formula you gave and it works just as I needed. Thank you.
I
have been using the military time consistently with my project, I suppose
I
just entered 10 to 2 for simplicity sake. As far as I can tell, using
military time seems to work well with what I have set up and with that
awesome formula you gave me, but does it make a difference to excel
whether I
use military time or standard?

"Fred Smith" wrote:

It's a lot easier when you show the formulas you have tried. Otherwise,
we
are just guessing. As a guess, try the following:

1. For the full day, limit the result to 8 hours by using the Min
function,
as in: =min(d14-c14,time(8,0,0))
2. For the half day, the formula is the same (to get a difference in
time,
it's just subtraction: =d14-c14). If you are getting bad results, it's
likely the data that's wrong. You state your times for a full day are
8:00
to 17:00, yet for a half day is 10 to 2. Is the solution as simple as
entering consistent times -- ie, 10:00 to 14:00?

Regards,
Fred.

"Jessie" wrote in message
...
I am trying to prepare an employee time off log for 20 employees. I have
read
through the discussions, but can't seem to find what I need. Any help
would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am to
5pm.
When someone takes off a full day, only 8 hours needs to be charged
against
him, instead of 9. I need a formula that will display only 8 hours used
instead of 9 when I subtract the two time cells of one containing 8:00,
the
other containing 17:00. I also need this formula to calculate half
days,
such
as when an employee leaves maybe from 10 to 2, for example. Everything
I
have
tried displays either 0.00, FALSE, or the correct number appears but
then
it
messes up my other formula for "time allowed minus time used equals
time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking
off a
full day) and d14 is the time returning (or 5:00, end of the day) and
e14
being the difference between the two cells.

Time formula
 

I think you still haven't told us what formula you are using to try to add,
what values you were trying to add, what result you got, and what result you
expected.
--
David Biddulph

"Jessie" wrote in message
...
The min formula works very well for determining the differnce between the
two
times for one day, but it does not sum the total over serveral days at the
bottom of the sheet. Any ideas?

"Fred Smith" wrote:

The format of a cell (other than Text) has no effect on Excel functions.
You
can format the time any way you want and the formulas will still work.

Regards,
Fred.

"Jessie" wrote in message
...
I tried the min formula you gave and it works just as I needed. Thank
you.
I
have been using the military time consistently with my project, I
suppose
I
just entered 10 to 2 for simplicity sake. As far as I can tell, using
military time seems to work well with what I have set up and with that
awesome formula you gave me, but does it make a difference to excel
whether I
use military time or standard?

"Fred Smith" wrote:

It's a lot easier when you show the formulas you have tried.
Otherwise,
we
are just guessing. As a guess, try the following:

1. For the full day, limit the result to 8 hours by using the Min
function,
as in: =min(d14-c14,time(8,0,0))
2. For the half day, the formula is the same (to get a difference in
time,
it's just subtraction: =d14-c14). If you are getting bad results, it's
likely the data that's wrong. You state your times for a full day are
8:00
to 17:00, yet for a half day is 10 to 2. Is the solution as simple as
entering consistent times -- ie, 10:00 to 14:00?

Regards,
Fred.

"Jessie" wrote in message
...
I am trying to prepare an employee time off log for 20 employees. I
have
read
through the discussions, but can't seem to find what I need. Any
help
would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am
to
5pm.
When someone takes off a full day, only 8 hours needs to be charged
against
him, instead of 9. I need a formula that will display only 8 hours
used
instead of 9 when I subtract the two time cells of one containing
8:00,
the
other containing 17:00. I also need this formula to calculate half
days,
such
as when an employee leaves maybe from 10 to 2, for example.
Everything
I
have
tried displays either 0.00, FALSE, or the correct number appears but
then
it
messes up my other formula for "time allowed minus time used equals
time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking
off a
full day) and d14 is the time returning (or 5:00, end of the day)
and
e14
being the difference between the two cells.

Time formula
 

Try using a format of [hh]:mm to display the result.

Regards,
Fred.

"Jessie" wrote in message
...
The min formula works very well for determining the differnce between the
two
times for one day, but it does not sum the total over serveral days at the
bottom of the sheet. Any ideas?

"Fred Smith" wrote:

The format of a cell (other than Text) has no effect on Excel functions.
You
can format the time any way you want and the formulas will still work.

Regards,
Fred.

"Jessie" wrote in message
...
I tried the min formula you gave and it works just as I needed. Thank
you.
I
have been using the military time consistently with my project, I
suppose
I
just entered 10 to 2 for simplicity sake. As far as I can tell, using
military time seems to work well with what I have set up and with that
awesome formula you gave me, but does it make a difference to excel
whether I
use military time or standard?

"Fred Smith" wrote:

It's a lot easier when you show the formulas you have tried.
Otherwise,
we
are just guessing. As a guess, try the following:

1. For the full day, limit the result to 8 hours by using the Min
function,
as in: =min(d14-c14,time(8,0,0))
2. For the half day, the formula is the same (to get a difference in
time,
it's just subtraction: =d14-c14). If you are getting bad results, it's
likely the data that's wrong. You state your times for a full day are
8:00
to 17:00, yet for a half day is 10 to 2. Is the solution as simple as
entering consistent times -- ie, 10:00 to 14:00?

Regards,
Fred.

"Jessie" wrote in message
...
I am trying to prepare an employee time off log for 20 employees. I
have
read
through the discussions, but can't seem to find what I need. Any
help
would
be most appreciated.

Our employees are allowed 120 vacation hours and they work from 8am
to
5pm.
When someone takes off a full day, only 8 hours needs to be charged
against
him, instead of 9. I need a formula that will display only 8 hours
used
instead of 9 when I subtract the two time cells of one containing
8:00,
the
other containing 17:00. I also need this formula to calculate half
days,
such
as when an employee leaves maybe from 10 to 2, for example.
Everything
I
have
tried displays either 0.00, FALSE, or the correct number appears but
then
it
messes up my other formula for "time allowed minus time used equals
time
remaining." All of my cells are set to the h:mm setting.

I have my cells set up as c14 as the time leaving (or 8:00 if taking
off a
full day) and d14 is the time returning (or 5:00, end of the day)
and
e14
being the difference between the two cells.