Time formula

[Jessie]

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.

 

[Fred Smith]

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]

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]

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]

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]

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

Regards,
Fred.