Far too many "IF"s for my own good
 

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.


---
Message posted from http://www.ExcelForum.com/

Far too many "IF"s for my own good
 

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.

--
Regards,
Tom Ogilvy

"Dave WL" wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

Far too many "IF"s for my own good
 

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





-----Original Message-----
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)+(MINUT E
($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.


---
Message posted from http://www.ExcelForum.com/

.

Far too many "IF"s for my own good
 

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 19 P
7 <9 B


Happy New Year

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address
"Dave WL" wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/