+ Reply to Thread
Results 1 to 9 of 9

simple math equation

  1. #1
    Richard
    Guest

    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!

  2. #2
    Roger Govier
    Guest

    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
    > Thanks in advance!




  3. #3
    Richard
    Guest

    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
    > > Thanks in advance!

    >
    >
    >


  4. #4
    Roger Govier
    Guest

    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
    >> > Thanks in advance!

    >>
    >>
    >>




  5. #5
    Richard
    Guest

    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. #6
    Richard
    Guest

    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. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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. #8
    Richard
    Guest

    Re: simple math equation

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


  9. #9
    Roger Govier
    Guest

    Re: simple math equation

    Nice correction, Dayylonglegs. Thank you.

    --
    Regards

    Roger Govier


    "daddylonglegs"
    <daddylonglegs.23fmlm_1140278101.1538@excelforum-nospam.com> wrote in
    message
    news:daddylonglegs.23fmlm_1140278101.1538@excelforum-nospam.com...
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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