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

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>