Forumla help needed

[yorkshirewhite]

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

 

[Bob Phillips]

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"