Excel 2007 help please…

I’ve got a spreadsheet for a work rota.
The shifts are entered as text i.e. day, night, leave etc etc.

I want to calculate how many hours someone works in a week.

How do I ‘make’ excel read it so that day=9 (as in 9 hours) for example.
So day+day = 18 and so on?

Many thanks!

DrP

quite a few ways (choose, lookup, sumif etc etc). The best way will depend on just how many “shift” categories you have and how you summarise the data.

Can you provide more info?

You need to start charging for this Stoner 🙂

=COUNTIF(D8:D12,”day”)*9 + COUNTIF(D8:D12,”halfday”)*4.5 etc

There are 9 different text shift patterns.
They are single words (LD, night, off)

Cheers

DrP

I find strangers on a mountain bike forum much more manageble clients than the headless chickens who’ve been raising my blood pressure today….

Oh, and the data is summarised in a calender view, so I want to be able to work out how many hours someone’s worked in a given week (the 7 days run vertically, with a ‘gap’ at the bottom for the hours worked)

There are about 15 people on the rota, and the rota runs for many months! So a way of doing it ‘multipally’ would be great.

DrP

so DRP
for a given worker, you would have a column of words (LD, night, off) and for each of those words theres a value in hours (9, 4.5, 0, 9)

and you want to total up the hours? or do you want to total up the hours classed as LD, classed as night, classed as off etc?

drp **** it, I have wine, email me your workbook.

Stoner – as you said, it’s a column of words, and I want each work to represent a number, and I want to SUM the total of those words in the week

i.e
day
day
day
off
off
off
off

would equal 3 days = 3*13 + 4*0 = 39.

Once I know haw to make excel ‘read’ the word ‘day’ as a number, i should be fine!

Just seen your latest post – I couldn’t ask that of you, but very kind!

DrP

readin day as a number is easy, its if you need it to read day, night, off, having her period, half day etc all at the same time which changes the best way of doing it.

tinribz is the “easiest” in terms of code, but its ugly if you have many words

Ive got a feeling you’re going to need to insert an extra step because you are effectively building a 3 dimensional database which good’ol 2D excel has trouble with 🙂

let me ponder

ta!
Really there’s only 4 ‘words’ that need to be converted to figures – the rest are ‘0’ so can be left alone (if excel just ignores words that carry no numerical value?)

DrP

if there’s only 4 then Id go with tinribz.

eg:

=((countif(A1:A30, “day”)*9)+((countif(A1:A30, “night”)*9)+((countif(A1:A30, “halfday”)*4.5)+((countif(A1:A30, “bungee day”)*1)

where A1:A30 is your list of words

I would do it like tinribz:
=COUNTIF(D8:D12,”day”)*9 + COUNTIF(D8:D12,”halfday”)*4.5

hahah got it, this is reet purty!

Its an array forumla so when you enter it finish not by pressing enter, but ctrl+shift+enter at the same time (it will give it curly brackets)

=SUM(LOOKUP(A1:A30, {“day”,”night”,”halfday”,”bungee”}, {9,9,4.5,1}))

you can see the pattern to do change what you want.

after a crap day with clients, that has made my night. 🙂

Im off for a wa nk.

Very elegant

Very elegant indeed! Thanks 🙂

Here’s a simple one – it messes up when a cell (yup – I said cell!) is empty.
How do i replace empty cells with ‘nil’ or ‘zero’ (to count as 0 hours)

DrP

=SUM(IF(A1:A6=””, 0, LOOKUP(A1:A6, {“a”,”b”,”c”,”d”,””}, {1,2,3,4,0})))

the “” means blank cell.
if there’s anything in the cell that isnt day, night etc, or blank, then it will ignore it. Also, the “” next to the “d” means that if anything else exists in the cell that isnt a, b, c, d or blank, it will give it calue 0

Nope – can’t get it working!

I can’t figure out how to enter that in the function box.
Using lookup, when I try to enter… “ANAES”, “NIGHT”, “LD”, “ICU”… it says ‘invalid’!

DrP

dont use the form, type it in as shown replacing the entries as you need then finish with ctrl+shift+enter

=SUM(IF(A1:A6=””, 0, LOOKUP(A1:A6, {“ANAES”,”NIGHT”,”LD”,”ICU”,””}, {9,9,3,12,0})))