Far too many "IF"s for my own good

  • Thread starter Thread starter Dave WL
  • Start date Start date
D

Dave WL

Here's a challenge for anyone who thinks they're good enough. It's
given me an extreme headache. I have two columns of time data (Start
and End). From this I needed to calculate first time difference as a
decimal - done - no problem. Now what I need to do is split that
decimal.

I need to set it up so that the time is split into Basic and Premium
time. If the hours are after 7pm and before 7am they are "premium"
hours - if before "7pm" they are basic. I managed to get it so that it
would come back with the right hours with this formula:

=IF(HOUR($D54)>=19,(((HOUR($D54)-19)+(MINUTE($D54)/60))*(E54+F54)),IF(HOUR($D54)<7,(((HOUR($D54)+5)+(MINUTE($D54)/60))*(E54+F54)),0))

Column D being the end time (column C would the the start time). But
this only works if the start time if before 7pm. If both the start and
end time are in the "Premium" time then it doesn't. And then if the
end time is after midnight it works ok (again presuming that start time
is before 7pm) but if the end time IS midnight then I of course get a
huge negative premium time. I managed to get some of the way to fixing
this but only with about 15 "IF"s strung together. There MUST be an
easier way.

PLEASE HELP!!!!!!

I'm only 23 and this has already given me grey hairs.
 
This was also multiposted to the probably more appropriate group
Microsoft.Public.Excel.Worksheet.functions so any potential solution
provider might want to check there as well before investing a lot of time in
developing an answer.
 
of course there's an easier way, but first, your existing
formula could be re-written as follows

=( (HOUR($D55)>=19) * ( HOUR($D55)-19+MINUTE($D55)/60 )
+ (HOUR($D55)<7) * ( HOUR($D55)+5+MINUTE($D55)/60 ) )
* (E55+F55)


Now then. Consider using a UDF - User Defined Function.
This gives a ot of flexibility
the following code driopped into a standard module will
give you the idea...but careful..I haven't debugged it !!

Function MyTime(sTarget As Double, Dval As Double, Eval
As Double) As Double

Dim hr As Double
Dim mn As Double

hr = Hour(sTarget)
mn = Minute(sTarget) / 60

Select Case hr
Case Is >= 19
hr = hr - 19

Case Is < 7
hr = hr + 5
Case Else
hr = 0
mn = 0
End Select

MyTime = (hr + mn) + (Dval + Eval)

End Function


Patrick Molloy
Microsoft Excel MVP
 
Thanks - my brain had given up after far too many hours of trying to
sort this sheet out

Cheers

Dave
 
Yeah - my apologies for that - I was just in the wrong forum when
posted - my mistake. I still would lie help from anyone who's willin
to help - my head is still hurting!!

Cheers,

Dav
 
Believe I have done - seems to be working anyway. However it still
doesn't seem to like people who have finished at exactly midnight.

I have a cut down one (without the additions just added)

Maybe you can see an easier way to deal with the wretched thing.

And you can have a laugh at the number of brackets I use in my
formulae!!

:)

Regards

Dave
 
Believe I have done - seems to be working anyway. However it still
doesn't seem to like people who have finished at exactly midnight.

I have a cut down one (without the additions just added)

Maybe you can see an easier way to deal with the wretched thing.

And you can have a laugh at the number of brackets I use in my
formulae!!

:)

Regards

Dave

Attachment filename: cut down.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=393118
 
Problem is not stated too clearly but I would try using an additional column
to get values of 1 or 2 ( or P and B) depending on the time factors. Then
use this column to do the calculation. Can you set the problem out in a
table
StartTime EndTime Status
7 <9 B
Click to expand...


Happy New Year
 
Back
Top