+ Reply to Thread
Results 1 to 2 of 2

Forumla help needed

  1. #1
    Registered User
    Join Date
    02-11-2006
    Posts
    33

    Forumla help needed

    Need help with this formula could someone please help me

    I need a formula whereby the result sums up days and hours. However a day is only 8 hours long.

    The user types in S (for a full day) s1 (for 1 hour) s2 (for 2 hours) s3 (for 3 hours etc up to 7 hours

    So the formula needs to say for instance if i find an S and an S1 and an S2, that would be 1 day and 3 hours shown as (1.3)

    Or if I find an S7 and an S2 that would be 1 day and 1 hour shown as (1.1)

    Any help would be appreciated thank you

  2. #2
    Bob Phillips
    Guest

    Re: Forumla help needed

    This will show it as 1.125, not 1.1

    =SUM(IF(EXACT(A1:A20,"S"),1,IF(EXACT(LEFT(A1:A20,1),"s"),RIGHT(A1:A20)/8)))

    which is an array formula, it should be committed with Ctrl-Shift-Enter, not
    just Enter.

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "yorkshirewhite"
    <yorkshirewhite.24kmiy_1142190900.5452@excelforum-nospam.com> wrote in
    message news:yorkshirewhite.24kmiy_1142190900.5452@excelforum-nospam.com...
    >
    > Need help with this formula could someone please help me
    >
    > I need a formula whereby the result sums up days and hours. However a
    > day is only 8 hours long.
    >
    > The user types in S (for a full day) s1 (for 1 hour) s2 (for 2 hours)
    > s3 (for 3 hours etc up to 7 hours
    >
    > So the formula needs to say for instance if i find an S and an S1 and
    > an S2, that would be 1 day and 3 hours shown as (1.3)
    >
    > Or if I find an S7 and an S2 that would be 1 day and 1 hour shown as
    > (1.1)
    >
    > Any help would be appreciated thank you
    >
    >
    > --
    > yorkshirewhite
    > ------------------------------------------------------------------------
    > yorkshirewhite's Profile:

    http://www.excelforum.com/member.php...o&userid=31448
    > View this thread: http://www.excelforum.com/showthread...hreadid=521528
    >




+ 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