simple math equation

If you're working in hours, say 8 hour increments. How would you write the
formula to reflect every 2 hours instead it's displaying halves and
quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs or .6hrs)
Example: If I've schelduled or taken 42 hours vacation and I have 20 days
available. I'd like the results to display ( 5 days and 2 hrs) used. (14 days
and 6 hrs available) This formula works fine for the scheduled vacation but
gives .50, .25 results =SUM(HList!D:D)/8 This formula works fine for the
available vacation but still gives .50, .25 results
Thanks in advance!

Hi Richard

Try
=INT(SUM(HList!D:D)/8)&"Days "&MOD(SUM(HList!D:D),8)&" hours"

--
Regards

Roger Govier


"Richard" <[email protected]> wrote in message
news:[email protected]...
> If you're working in hours, say 8 hour increments. How would you write
> the
> formula to reflect every 2 hours instead it's displaying halves and
> quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs or
> .6hrs)
> Example: If I've schelduled or taken 42 hours vacation and I have 20
> days
> available. I'd like the results to display ( 5 days and 2 hrs) used.
> (14 days
> and 6 hrs available) This formula works fine for the scheduled
> vacation but
> gives .50, .25 results =SUM(HList!D:D)/8 This formula works fine for
> the
> available vacation but still gives .50, .25 results
> Thanks in advance!

Thank you so much! It works perfect for the Scheduled or used vacation but
the Available vacation is getting a #VALUE! probably due to the text? Thanks
so much. It's still perfect!

"Roger Govier" wrote:

> Hi Richard
>
> Try
> =INT(SUM(HList!D:D)/8)&"Days "&MOD(SUM(HList!D:D),8)&" hours"
>
> --
> Regards
>
> Roger Govier
>
>
> "Richard" <[email protected]> wrote in message
> news:[email protected]...
> > If you're working in hours, say 8 hour increments. How would you write
> > the
> > formula to reflect every 2 hours instead it's displaying halves and
> > quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs or
> > .6hrs)
> > Example: If I've schelduled or taken 42 hours vacation and I have 20
> > days
> > available. I'd like the results to display ( 5 days and 2 hrs) used.
> > (14 days
> > and 6 hrs available) This formula works fine for the scheduled
> > vacation but
> > gives .50, .25 results =SUM(HList!D:D)/8 This formula works fine for
> > the
> > available vacation but still gives .50, .25 results
> > Thanks in advance!
>
>
>

Hi Richard

Didn't notice the second part of your request.
Either use 20 for the number of days total holiday available at the
beginning of the formula, or put 20 in a cell (A1 in this example)

=A1-(INT(SUM(HList!D:D)/8)+1)&" Days "&8-MOD(SUM(HList!D:D),8)&" hours
left"

--
Regards

Roger Govier


"Richard" <[email protected]> wrote in message
news:[email protected]...
> Thank you so much! It works perfect for the Scheduled or used vacation
> but
> the Available vacation is getting a #VALUE! probably due to the text?
> Thanks
> so much. It's still perfect!
>
> "Roger Govier" wrote:
>
>> Hi Richard
>>
>> Try
>> =INT(SUM(HList!D:D)/8)&"Days "&MOD(SUM(HList!D:D),8)&" hours"
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Richard" <[email protected]> wrote in message
>> news:[email protected]...
>> > If you're working in hours, say 8 hour increments. How would you
>> > write
>> > the
>> > formula to reflect every 2 hours instead it's displaying halves and
>> > quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs
>> > or
>> > .6hrs)
>> > Example: If I've schelduled or taken 42 hours vacation and I have
>> > 20
>> > days
>> > available. I'd like the results to display ( 5 days and 2 hrs)
>> > used.
>> > (14 days
>> > and 6 hrs available) This formula works fine for the scheduled
>> > vacation but
>> > gives .50, .25 results =SUM(HList!D:D)/8 This formula works fine
>> > for
>> > the
>> > available vacation but still gives .50, .25 results
>> > Thanks in advance!
>>
>>
>>

Once again It's perfect! Thanks so very much!!!

"Roger Govier" wrote:

> Hi Richard
>
> Didn't notice the second part of your request.
> Either use 20 for the number of days total holiday available at the
> beginning of the formula, or put 20 in a cell (A1 in this example)
>
> =A1-(INT(SUM(HList!D:D)/8)+1)&" Days "&8-MOD(SUM(HList!D:D),8)&" hours
> left"
>
> --
> Regards
>
> Roger Govier
>
>
> "Richard" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you so much! It works perfect for the Scheduled or used vacation
> > but
> > the Available vacation is getting a #VALUE! probably due to the text?
> > Thanks
> > so much. It's still perfect!
> >
> > "Roger Govier" wrote:
> >
> >> Hi Richard
> >>
> >> Try
> >> =INT(SUM(HList!D:D)/8)&"Days "&MOD(SUM(HList!D:D),8)&" hours"
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Richard" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > If you're working in hours, say 8 hour increments. How would you
> >> > write
> >> > the
> >> > formula to reflect every 2 hours instead it's displaying halves and
> >> > quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs
> >> > or
> >> > .6hrs)
> >> > Example: If I've schelduled or taken 42 hours vacation and I have
> >> > 20
> >> > days
> >> > available. I'd like the results to display ( 5 days and 2 hrs)
> >> > used.
> >> > (14 days
> >> > and 6 hrs available) This formula works fine for the scheduled
> >> > vacation but
> >> > gives .50, .25 results =SUM(HList!D:D)/8 This formula works fine
> >> > for
> >> > the
> >> > available vacation but still gives .50, .25 results
> >> > Thanks in advance!
> >>
> >>
> >>
>
>
>

I'm almost embarrased to ask for help again. But... on the last formula the
hours doesn't change to 0. It shows the correct Days but instead of 0 it
shows 8 for the hours. The first formula works perfect.

"Roger Govier" wrote:

> Hi Richard
>
> Didn't notice the second part of your request.
> Either use 20 for the number of days total holiday available at the
> beginning of the formula, or put 20 in a cell (A1 in this example)
>
> =A1-(INT(SUM(HList!D:D)/8)+1)&" Days "&8-MOD(SUM(HList!D:D),8)&" hours
> left"
>
> --
> Regards
>
> Roger Govier
>
>
> "Richard" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you so much! It works perfect for the Scheduled or used vacation
> > but
> > the Available vacation is getting a #VALUE! probably due to the text?
> > Thanks
> > so much. It's still perfect!
> >
> > "Roger Govier" wrote:
> >
> >> Hi Richard
> >>
> >> Try
> >> =INT(SUM(HList!D:D)/8)&"Days "&MOD(SUM(HList!D:D),8)&" hours"
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Richard" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > If you're working in hours, say 8 hour increments. How would you
> >> > write
> >> > the
> >> > formula to reflect every 2 hours instead it's displaying halves and
> >> > quarters(.50 and .25) I'd like for it to display (.2 hrs or .4hrs
> >> > or
> >> > .6hrs)
> >> > Example: If I've schelduled or taken 42 hours vacation and I have
> >> > 20
> >> > days
> >> > available. I'd like the results to display ( 5 days and 2 hrs)
> >> > used.
> >> > (14 days
> >> > and 6 hrs available) This formula works fine for the scheduled
> >> > vacation but
> >> > gives .50, .25 results =SUM(HList!D:D)/8 This formula works fine
> >> > for
> >> > the
> >> > available vacation but still gives .50, .25 results
> >> > Thanks in advance!
> >>
> >>
> >>
>
>
>

Hello Richard,

try this amendment to Roger's formula

=A1-CEILING(SUM(Hlist!D:D)/8,1)&" Days "&MOD(8-SUM(Hlist!D:D),8)&" hours left"

Yes!!! That did the trick, you guys are amaging! Thank you!

"daddylonglegs" wrote:

>
> Hello Richard,
>
> try this amendment to Roger's formula
>
> =A1-CEILING(SUM(Hlist!D:D)/8,1)&" Days "&MOD(8-SUM(Hlist!D:D),8)&"
> hours left"
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=513975
>
>

Nice correction, Dayylonglegs. Thank you.

--
Regards

Roger Govier


"daddylonglegs"
<[email protected]> wrote in
message
news:[email protected]...
>
> Hello Richard,
>
> try this amendment to Roger's formula
>
> =A1-CEILING(SUM(Hlist!D:D)/8,1)&" Days "&MOD(8-SUM(Hlist!D:D),8)&"
> hours left"
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile:
> http://www.excelforum.com/member.php...o&userid=30486
> View this thread:
> http://www.excelforum.com/showthread...hreadid=513975
>