Hi,

Here is what I am trying to do:

I have a spreadsheet that keeps track of employees vacation and sick
time.

The spreadsheet is formatted to column A is the employee name, columns
B - AB are days of the month.

I would like to have the manager be able to enter in the time off as
either S8 or V8 (8 representing the number of hours off the employee
had S = sick V = vacation).

I would like Column AC to then calcuate the total sick hours and AD to
calcuate the total of vacation hours.

I played around with a few formulas but could not figure out a way to
do it. Is there a way to have this done? What is the correct formula?

Thank you very kindly for the input!

=SUM(IF(LEFT(B2:AB2,1)="V",--SUBSTITUTE(B2:AB2,"V","")))

and

=SUM(IF(LEFT(B2:AB2,1)="S",--SUBSTITUTE(B2:AB2,"S","")))


which are array formulae, so commit with Ctrl-Shift-Enter


--

HTH

RP
(remove nothere from the email address if mailing direct)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Here is what I am trying to do:
>
> I have a spreadsheet that keeps track of employees vacation and sick
> time.
>
> The spreadsheet is formatted to column A is the employee name, columns
> B - AB are days of the month.
>
> I would like to have the manager be able to enter in the time off as
> either S8 or V8 (8 representing the number of hours off the employee
> had S = sick V = vacation).
>
> I would like Column AC to then calcuate the total sick hours and AD to
> calcuate the total of vacation hours.
>
> I played around with a few formulas but could not figure out a way to
> do it. Is there a way to have this done? What is the correct formula?
>
> Thank you very kindly for the input!
>

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

For vacation hours:

=SUM(IF(LEFT(B1:H1)="v",MID(B1:H1,2,10)*1))

Replace "v" with "s" for sick hours.

Biff

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Here is what I am trying to do:
>
> I have a spreadsheet that keeps track of employees vacation and sick
> time.
>
> The spreadsheet is formatted to column A is the employee name, columns
> B - AB are days of the month.
>
> I would like to have the manager be able to enter in the time off as
> either S8 or V8 (8 representing the number of hours off the employee
> had S = sick V = vacation).
>
> I would like Column AC to then calcuate the total sick hours and AD to
> calcuate the total of vacation hours.
>
> I played around with a few formulas but could not figure out a way to
> do it. Is there a way to have this done? What is the correct formula?
>
> Thank you very kindly for the input!
>

A non-arrray solution
=SUMPRODUCT(--(LEFT(B2:AC2)="s"),--(--(RIGHT(TEXT(B2:AC2,"00")))))
the problem was what to do with empty cells - TEXT solved this
My solution works only when hours are one digit.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Here is what I am trying to do:
>
> I have a spreadsheet that keeps track of employees vacation and sick
> time.
>
> The spreadsheet is formatted to column A is the employee name, columns
> B - AB are days of the month.
>
> I would like to have the manager be able to enter in the time off as
> either S8 or V8 (8 representing the number of hours off the employee
> had S = sick V = vacation).
>
> I would like Column AC to then calcuate the total sick hours and AD to
> calcuate the total of vacation hours.
>
> I played around with a few formulas but could not figure out a way to
> do it. Is there a way to have this done? What is the correct formula?
>
> Thank you very kindly for the input!
>

If people are always sick or on Vacation for 8 hours then enter only V or S
and use:

=COUNTIF(B2:AB2,"S")*8

If they can be on sick for different numbers of hours try:

=SUM((LEFT(B24:AB24,1)="V")*(IF(ISNUMBER(--(RIGHT(B24:AB24,1))),RIGHT(B24:AB24,1),0)))

This is an array formula entered with Ctrl + Shift + Enter not just Enter

--
HTH

Sandy
(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Here is what I am trying to do:
>
> I have a spreadsheet that keeps track of employees vacation and sick
> time.
>
> The spreadsheet is formatted to column A is the employee name, columns
> B - AB are days of the month.
>
> I would like to have the manager be able to enter in the time off as
> either S8 or V8 (8 representing the number of hours off the employee
> had S = sick V = vacation).
>
> I would like Column AC to then calcuate the total sick hours and AD to
> calcuate the total of vacation hours.
>
> I played around with a few formulas but could not figure out a way to
> do it. Is there a way to have this done? What is the correct formula?
>
> Thank you very kindly for the input!
>

some garbage was left over from my test - correc this to
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:uPJ%(E-Mail Removed)...
>A non-arrray solution
> =SUMPRODUCT(--(LEFT(B2:AC2)="s"),--(--(RIGHT(TEXT(B2:AC2,"00")))))
> the problem was what to do with empty cells - TEXT solved this
> My solution works only when hours are one digit.
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Hi,
>>
>> Here is what I am trying to do:
>>
>> I have a spreadsheet that keeps track of employees vacation and sick
>> time.
>>
>> The spreadsheet is formatted to column A is the employee name, columns
>> B - AB are days of the month.
>>
>> I would like to have the manager be able to enter in the time off as
>> either S8 or V8 (8 representing the number of hours off the employee
>> had S = sick V = vacation).
>>
>> I would like Column AC to then calcuate the total sick hours and AD to
>> calcuate the total of vacation hours.
>>
>> I played around with a few formulas but could not figure out a way to
>> do it. Is there a way to have this done? What is the correct formula?
>>
>> Thank you very kindly for the input!
>>
>
>

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> =SUM(IF(LEFT(B2:AB2,1)="V",--SUBSTITUTE(B2:AB2,"V","")))
>
> and
>
> =SUM(IF(LEFT(B2:AB2,1)="S",--SUBSTITUTE(B2:AB2,"S","")))
>
>
> which are array formulae, so commit with Ctrl-Shift-Enter
>

I like it!

--
Regards

Sandy
(E-Mail Removed)
(E-Mail Removed) with @tiscali.co.uk

You guys are awesome!

Thank you for the quick and easy responses!

Hi Bernard (and others)

The OP has array solutions to his problem from Bob, Sandy and Biff,
which will solve his problem.

As someone who tries to avoid array formulae when possible, I was
examining your SUMPRODUCT solution.
I decided the following simple modification will deal with situations
(should they arise) of say 10.25 hours, whereas you stated your solution
dealt with single digit values. What I did was
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(MID(TEXT(B2:F2,"00"),2,5)))
and all appeared to be well and with data in B2:F2 of s2, v4, s10.25,
empty , empty,
it produced the desired result of 12.25

However, with just a single letter "s" entered into the first empty cell
in the range, I got a #VALUE error (as I did with your formula also).
This I can fully understand, and thought about ways of solving the
problem and came up with
=SUMPRODUCT(--(LEFT(B2:F2)="s"),--(IF(LEN(B2:F2)=1,0,MID(TEXT(B2:F2,"00"),2,5))))
This still produces a #VALUE result in the cell containing the formula,
but curiously when using F9 to evaluate parts of the formula in the
formula toolbar, I get the same result of 12.25.
Using F9 on the first part produces {1,0,1,1,0} and on the second part
produces {10,25,0,2,0,0}, F9 on the whole formula produces, as I have
said, 12.25.

Do you, or anyone else, have anything to suggest as to why this is, or
what the solution might be, purely out of academic interest?


--
Regards

Roger Govier


"Bernard Liengme" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> some garbage was left over from my test - correc this to
> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bernard Liengme" <(E-Mail Removed)> wrote in message
> news:uPJ%(E-Mail Removed)...
>>A non-arrray solution
>> =SUMPRODUCT(--(LEFT(B2:AC2)="s"),--(--(RIGHT(TEXT(B2:AC2,"00")))))
>> the problem was what to do with empty cells - TEXT solved this
>> My solution works only when hours are one digit.
>> best wishes
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Hi,
>>>
>>> Here is what I am trying to do:
>>>
>>> I have a spreadsheet that keeps track of employees vacation and sick
>>> time.
>>>
>>> The spreadsheet is formatted to column A is the employee name,
>>> columns
>>> B - AB are days of the month.
>>>
>>> I would like to have the manager be able to enter in the time off as
>>> either S8 or V8 (8 representing the number of hours off the employee
>>> had S = sick V = vacation).
>>>
>>> I would like Column AC to then calcuate the total sick hours and AD
>>> to
>>> calcuate the total of vacation hours.
>>>
>>> I played around with a few formulas but could not figure out a way
>>> to
>>> do it. Is there a way to have this done? What is the correct
>>> formula?
>>>
>>> Thank you very kindly for the input!
>>>
>>
>>
>
>

Hi Roger,
I tried your formula with a single SO in B2 (no digit) and also get #VALUE!
I use EXCEL 2003 so I tried the Evaluate Formula tool; after 6 steps I get
=SUMPRODUCT({1,1,1,0,0},--{"","2","5","0","4"}))
That null as the first item in the second array is the big problem - my
formula does the same thing.
Excel likes to treat blanks as zero (=A10+2 give 2 if A10 is empty) so why
does SUMPRODUCT not follow the general behaviour? I expect it is a C++
thing.
After 30 mins for playing I gave up.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Roger Govier" <(E-Mail Removed)> wrote in message
news:O%(E-Mail Removed)...
> Hi Bernard (and others)
>
> The OP has array solutions to his problem from Bob, Sandy and Biff, which
> will solve his problem.
>
> As someone who tries to avoid array formulae when possible, I was
> examining your SUMPRODUCT solution.
> I decided the following simple modification will deal with situations
> (should they arise) of say 10.25 hours, whereas you stated your solution
> dealt with single digit values. What I did was
> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(MID(TEXT(B2:F2,"00"),2,5)))
> and all appeared to be well and with data in B2:F2 of s2, v4, s10.25,
> empty , empty,
> it produced the desired result of 12.25
>
> However, with just a single letter "s" entered into the first empty cell
> in the range, I got a #VALUE error (as I did with your formula also).
> This I can fully understand, and thought about ways of solving the problem
> and came up with
> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(IF(LEN(B2:F2)=1,0,MID(TEXT(B2:F2,"00"),2,5))))
> This still produces a #VALUE result in the cell containing the formula,
> but curiously when using F9 to evaluate parts of the formula in the
> formula toolbar, I get the same result of 12.25.
> Using F9 on the first part produces {1,0,1,1,0} and on the second part
> produces {10,25,0,2,0,0}, F9 on the whole formula produces, as I have
> said, 12.25.
>
> Do you, or anyone else, have anything to suggest as to why this is, or
> what the solution might be, purely out of academic interest?
>
>
> --
> Regards
>
> Roger Govier
>
>
> "Bernard Liengme" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> some garbage was left over from my test - correc this to
>> =SUMPRODUCT(--(LEFT(B2:F2)="s"),--(RIGHT(TEXT(B2:F2,"00"))))
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Bernard Liengme" <(E-Mail Removed)> wrote in message
>> news:uPJ%(E-Mail Removed)...
>>>A non-arrray solution
>>> =SUMPRODUCT(--(LEFT(B2:AC2)="s"),--(--(RIGHT(TEXT(B2:AC2,"00")))))
>>> the problem was what to do with empty cells - TEXT solved this
>>> My solution works only when hours are one digit.
>>> best wishes
>>> --
>>> Bernard V Liengme
>>> www.stfx.ca/people/bliengme
>>> remove caps from email
>>>
>>> <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Hi,
>>>>
>>>> Here is what I am trying to do:
>>>>
>>>> I have a spreadsheet that keeps track of employees vacation and sick
>>>> time.
>>>>
>>>> The spreadsheet is formatted to column A is the employee name, columns
>>>> B - AB are days of the month.
>>>>
>>>> I would like to have the manager be able to enter in the time off as
>>>> either S8 or V8 (8 representing the number of hours off the employee
>>>> had S = sick V = vacation).
>>>>
>>>> I would like Column AC to then calcuate the total sick hours and AD to
>>>> calcuate the total of vacation hours.
>>>>
>>>> I played around with a few formulas but could not figure out a way to
>>>> do it. Is there a way to have this done? What is the correct formula?
>>>>
>>>> Thank you very kindly for the input!
>>>>
>>>
>>>
>>
>>
>
>