(Msg. 1) Posted: Tue Nov 18, 2008 1:01 pm
Post subject: Time formula Archived from groups: microsoft>public>excel>misc (more info?)
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.
(Msg. 2) Posted: Tue Nov 18, 2008 3:31 pm
Post subject: Re: Time formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Jessie RemoveThis @discussions.microsoft.com> wrote in message
news:29B95BC0-D557-46D2-92D0-BFCBA1BB329F@microsoft.com...
>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.
>
(Msg. 3) Posted: Tue Nov 18, 2008 3:31 pm
Post subject: Re: Time formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Jessie RemoveThis @discussions.microsoft.com> wrote in message
> news:29B95BC0-D557-46D2-92D0-BFCBA1BB329F@microsoft.com...
> >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.
> >
>
>
(Msg. 4) Posted: Tue Nov 18, 2008 4:33 pm
Post subject: Re: Time formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Jessie.DeleteThis@discussions.microsoft.com> wrote in message
news:47B03734-2E1F-4AC8-AAE1-E0114359718A@microsoft.com...
>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" <Jessie.DeleteThis@discussions.microsoft.com> wrote in message
>> news:29B95BC0-D557-46D2-92D0-BFCBA1BB329F@microsoft.com...
>> >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.
>> >
>>
>>
(Msg. 5) Posted: Wed Nov 19, 2008 10:29 am
Post subject: Re: Time formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Jessie DeleteThis @discussions.microsoft.com> wrote in message
> news:47B03734-2E1F-4AC8-AAE1-E0114359718A@microsoft.com...
> >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" <Jessie DeleteThis @discussions.microsoft.com> wrote in message
> >> news:29B95BC0-D557-46D2-92D0-BFCBA1BB329F@microsoft.com...
> >> >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.
> >> >
> >>
> >>
>
>
(Msg. 6) Posted: Wed Nov 19, 2008 5:07 pm
Post subject: Re: Time formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Try using a format of [hh]:mm to display the result.
Regards,
Fred.
"Jessie" <Jessie.RemoveThis@discussions.microsoft.com> wrote in message
news:D81D890E-A469-4514-B4CA-B0C4CC085D2C@microsoft.com...
> 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" <Jessie.RemoveThis@discussions.microsoft.com> wrote in message
>> news:47B03734-2E1F-4AC8-AAE1-E0114359718A@microsoft.com...
>> >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" <Jessie.RemoveThis@discussions.microsoft.com> wrote in message
>> >> news:29B95BC0-D557-46D2-92D0-BFCBA1BB329F@microsoft.com...
>> >> >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.
>> >> >
>> >>
>> >>
>>
>>
(Msg. 7) Posted: Wed Nov 19, 2008 9:32 pm
Post subject: Re: Time formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Jessie.TakeThisOut@discussions.microsoft.com> wrote in message
news:D81D890E-A469-4514-B4CA-B0C4CC085D2C@microsoft.com...
> 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" <Jessie.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:47B03734-2E1F-4AC8-AAE1-E0114359718A@microsoft.com...
>> >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" <Jessie.TakeThisOut@discussions.microsoft.com> wrote in message
>> >> news:29B95BC0-D557-46D2-92D0-BFCBA1BB329F@microsoft.com...
>> >> >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.
>> >> >
>> >>
>> >>
>>
>>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office