Home Forum Chat Forum Excel 2007 help please…

Viewing 21 posts - 1 through 21 (of 21 total)
• DrP
Member

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

Stoner
Subscriber

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.

nickc
Subscriber

You need to start charging for this Stoner ðŸ™‚

tinribz
Member

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

DrP
Member

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

Cheers

DrP

Stoner
Subscriber

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

DrP
Member

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

Stoner
Subscriber

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?

Stoner
Subscriber

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

DrP
Member

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

Stoner
Subscriber

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

DrP
Member

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

Stoner
Subscriber

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

soma_rich
Member

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

Stoner
Subscriber

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.

nickc
Subscriber

Very elegant

DrP
Member

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

Stoner
Subscriber

=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

DrP
Member

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

Stoner
Subscriber

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

Stoner
Subscriber

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

Viewing 21 posts - 1 through 21 (of 21 total)

The topic ‘Excel 2007 help please…’ is closed to new replies.