# simple math equation

1. ## 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

2. ## Re: simple math equation

Hi Richard

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

--
Regards

Roger Govier

"Richard" <Richard@discussions.microsoft.com> wrote in message
news:679C5DC1-A186-4BEC-82E3-67404D67086F@microsoft.com...
> 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

3. ## Re: simple math equation

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" <Richard@discussions.microsoft.com> wrote in message
> news:679C5DC1-A186-4BEC-82E3-67404D67086F@microsoft.com...
> > 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

>
>
>

4. ## Re: simple math equation

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" <Richard@discussions.microsoft.com> wrote in message
news:928E6842-67FB-4666-87CC-F6C2B8DBB832@microsoft.com...
> 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" <Richard@discussions.microsoft.com> wrote in message
>> news:679C5DC1-A186-4BEC-82E3-67404D67086F@microsoft.com...
>> > 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

>>
>>
>>

5. ## Re: simple math equation

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" <Richard@discussions.microsoft.com> wrote in message
> news:928E6842-67FB-4666-87CC-F6C2B8DBB832@microsoft.com...
> > 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" <Richard@discussions.microsoft.com> wrote in message
> >> news:679C5DC1-A186-4BEC-82E3-67404D67086F@microsoft.com...
> >> > 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!
> >>
> >>
> >>

>
>
>

6. ## Re: simple math equation

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" <Richard@discussions.microsoft.com> wrote in message
> news:928E6842-67FB-4666-87CC-F6C2B8DBB832@microsoft.com...
> > 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" <Richard@discussions.microsoft.com> wrote in message
> >> news:679C5DC1-A186-4BEC-82E3-67404D67086F@microsoft.com...
> >> > 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!
> >>
> >>
> >>

>
>
>

7. 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"

8. ## Re: simple math equation

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

>
> 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"
>
>
> --
> ------------------------------------------------------------------------
>
>

9. ## Re: simple math equation

Nice correction, Dayylonglegs. Thank you.

--
Regards

Roger Govier

message
>
> 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"
>
>
> --
> ------------------------------------------------------------------------
> http://www.excelforum.com/member.php...o&userid=30486
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1