Little Excel help please

[Homer]

This should be a simple one for some of you lot

 

I have a column of times (in hours) which I need a formula to express in grouped text values. i.e.

 

0->5 returns "

5->24 returns "5-24 hours"

24->48 =returns "24-48 hours"

etc

 

The hour value would be in column A, the text value in column B

 

Ideally expressed in a single formula.

 

TIA!

[Purity14]

You would use the "IF" command.

 

=IF(AND(A2

[steady_dave]

TIA!

 

This Is Africa!

[jevansio]

By etc, does that the list can go on forever. The IF statement would be rather cumbersome if you had to nest it more than a handfull of times?

[Andy Blyth]

Nested IFs are your friend here...

 

Assuming the value you are checking is in A1:

 

=IF(A1

[Homer]

You would use the "IF" command.

 

=IF(AND(A2

 

Thanks, but thats not quite what I need.

 

The formula needs to work for all values, so whatever I enter in column A, the correct text value is entered in column B.

 

As far as I'm aware the IF command doesn't work with set ranges, only a single logical operator.

[jevansio]

Thanks, but thats not quite what I need.

 

The formula needs to work for all values, so whatever I enter in column A, the correct text value is entered in column B.

 

As far as I'm aware the IF command doesn't work with set ranges, only a single logical operator.

See Andy's post, it works if your hours stop at 48, any more and you'll need to keep nesting the IF's, you can see why eventually that method is very cumbersome

[Homer]

By etc, does that the list can go on forever. The IF statement would be rather cumbersome if you had to nest it more than a handfull of times?

 

Not forever, it's only for 5 or 6 values, the last one will be a greater than one.

 

Nested IFs are your friend here...

 

Assuming the value you are checking is in A1:

 

=IF(A1

 

Ahah, I was just thinking of that, but couldn't figure out the logic.

 

Thanks a lot, will go have a play with it now

[Gaz6002]

This Is Africa!

 

Toga in Angola!

 

(I do know what TIA means though, so I win)

[Al Massey]

Nested IFs are your friend here...

 

Assuming the value you are checking is in A1:

 

=IF(A1

 

 

That wont work, as all numbers entered below 48 will come up with

 

And it doesnt like the = sign

[rich_supra]

That wont work, as all numbers entered below 48 will come up with

 

And it doesnt like the = sign

 

got me thinking now. would it do that though as if the number is 4 it would have been met in first IF statement or do the subsequent ones overide that?

[Andy Blyth]

That wont work, as all numbers entered below 48 will come up with

 

And it doesnt like the = sign

 

Try it...

 

The 2nd level IF statement only gets evaluated if the top level IF returns false (i.e. > 5) etc.

[Al Massey]

well me and the wife are stumped as my excell wouldnt except the = sign

[jevansio]

Well i had the wife work on this and try and sort it out.

 

She works on excell all day every day and the IF function cant cope as there are too many variables in the text and will only work on a simple formula as in 0-5 =

 

We tried the SUMIF function but couldnt get excell to recognise the different variations.

 

she is going to have a play at work tomorrow and try and solve this.

No need, Andy has sorted it and posted a sheet as an example

[Al Massey]

Try it...

 

The 2nd level IF statement only gets evaluated if the top level IF returns false (i.e. > 5) etc.

 

Well you got me there,

 

How did you do it?

 

As im still stumped and as i said my excell wont except it

[jevansio]

Well you got me there,

 

How did you do it?

 

As im still stumped and as i said my excell wont except it

You're not entering it into cell A1 are you? What error are you getting?

[Al Massey]

You're not entering it into cell A1 are you? What error are you getting?

 

The error we had kept saying not to use = sign

[Homer]

Thanks guys, Andy's suggestion did the job Now, I have a more challenging one for you as I realised my original request didn't return the results I actually needed (it was the 2nd part of the data I need, but not the first)

 

What I actually need to acheive is the time, in hours between two dates, with weekends and out of hours removed.

 

I.e.

Column A

10/02/09 10:00

 

Column B

22/02/09 13:00

 

The formula needs to work out the number of work hours between those two dates. It needs to remove weekends and time between 08:00 and 16:00

 

I have managed to do this in the past (years ago) using a NETWORKDAYS formula, but can't remember how I did it and cannot make it work now.. Here's the formula I had last time:

 

=IF((B1-A1-NETWORKDAYS(A1,B1)+1)

 

Where 14 is the nuber of not-worked hours each day. I cannot get this to work now...

 

To complicate matters further, both column A and column B contain text values, not Excel pure date (numerical) format.

[jevansio]

To answer your complication use =DATEVALUE(some-text) to convert text to the internal date format, looking at other problem now, edit you'll need =TIMEVALUE() also to get the time part

[Andy Blyth]

To complicate matters further, both column A and column B contain text values, not Excel pure date (numerical) format.

 

To get the text values into excel Date / Time type try:

 

=DATEVALUE(A1)+TIMEVALUE(MID(A1,12,5))

 

Assuming the format:

 

DD/MM/YYYY HH:MM

[jevansio]

Off the top of my head a VB Script function is going to be the easiest to implement, a solution consisting of pure Excel function calls would be rather messy (although not impossible). Still working on it

[Homer]

To get the text values into excel Date / Time type try:

 

=DATEVALUE(A1)+TIMEVALUE(MID(A1,12,5))

 

Assuming the format:

 

DD/MM/YYYY HH:MM

 

Thanks again Andy, thats better than I'd managed, can't get it to work right yet, it's likely the date format I'm importing isn't quite right yet. Will give it another go later.

 

Off the top of my head a VB Script function is going to be the easiest to implement, a solution consisting of pure Excel function calls would be rather messy (although not impossible). Still working on it

 

Cheers Jay, VB code is also fine, but I've not written any for years so would probably be about as much good as my Excel skills

 

Should have stayed in a technical job, management rots the brain

[Andy Blyth]

My brain hurts and it's way past my bedtime...

 

I have a couple of code snippets for you to paste into a bigger function. The first returns the time since midnight excluding 08:00 to 16:00:

 

=IF(TIMEVALUE(B4)TIMEVALUE("16:00"),TIMEVALUE(B4)-TIMEVALUE("08:00"),TIMEVALUE("08:00"))))

 

(Assuming your time is in B4 in text format HH:MM)

 

The second returns the time remaining till midnight excluding the hours 08:00 to 16:00:

 

=IF(TIMEVALUE(B4)>TIMEVALUE("16:00"),1-TIMEVALUE(B4),(IF(TIMEVALUE(B4)

 

These should help calculating the number of hours on the first and last days in the range. You just need to find the number of integer days in between (not including the first and last and weekends), multiply by 16 (hours per day) and add the first and last days hours in.

 

HTH

[jevansio]

Darryl try this worksheet function:

 

Function WorkingHours(startDate As Double, endDate As Double) As Integer
   WorkingHours = 0
   For index = Int(startDate) To Int(endDate)
       If Weekday(index, vbMonday)             
           startHour = 0
           If index = Int(startDate) Then
               startHour = Hour(startDate)
           End If
           
           endHour = 23
           If index = Int(endDate) Then
               endHour = Hour(endTime)
           End If
           
           If startHour                 startHour = 8
           End If
           
           If endHour > 16 Then
               endHour = 16
           End If
               
           WorkingHours = WorkingHours + (endHour - startHour)
       End If
   Next index
End Function

[jevansio]

You will need to pass the converted dates (ie using Andys DATEVALUE/TIMEVALUE) to the function.

 

I am doing a sample sheet now which I will attach to show it's usage