Far too many "IF"s for my own good

[Dave WL]

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

I need to set it up so that the time is split into Basic and Premiu
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 i
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). Bu
this only works if the start time if before 7pm. If both the start an
end time are in the "Premium" time then it doesn't. And then if the en
time is after midnight it works ok (again presuming that start time i
before 7pm) but if the end time IS midnight then I of course get a hug
negative premium time. I managed to get some of the way to fixing thi
but only with about 15 "IF"s strung together. There MUST be an easie
way.

PLEASE HELP!!!!!!

I'm only 23 and this has already given me grey hairs

 

[Jim]

My self-esteem is in the crapper; I simply cannot imagine being "good
enough" to help, darn it. Perhaps someone will recommend you try VLOOKUP.

 

[Dave WL]

Thanks for this - I apologise for destroying your self esteem - join the
club - I have been beaten by a spreadsheet!!!!

Truly infuriating!

So - does anyon else know how to get the bloody thing to split the time
up so that it will add up time before and after 7pm??

Or am I being truly thick??

Hang on - I'll attach the .xls - everyone can have a good laugh at how
many brackets I use in my formulas!!



Dave

Attachment filename: cut down.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=393089

 

[Harlan Grove]

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.

It's not a matter of being 'good enough', it a matter of being patient. Get
married and have a few kids. Nothing much will seem difficult after you get used
to making family decisions by consensus (just ask your parents).

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))

The (HOUR($D54)+5) in the second IF's TRUE-part seems to mean premium time
started at 7:00 PM the previous day. If so, then you'd be better off adding 17
hours to the time in D54 and only dealing with resulting PM times.

=MAX(0,MOD(24*$D54+17,24)-12)*(E54+F54)

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. . . .

Where does column C come into play in your previous formula?

. . . 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.

There is.

If you have start time in column C and end time in column D, and *IF* they'll
*ALWAYS* be no more than 24 hours different, then the portion of the period
between them in premium time *hours* (7:00 PM one day to 7:00 AM the following
day) is given by

=24*((D54<=C54)*(1-BTE+BTB)+MIN(BTB,D54)-MIN(BTB,C54)+MAX(BTE,D54)-MAX(BTE,C54))

where BTB and BTE are names defined, respectively, as =TIME(7,0,0) and
=TIME(19,0,0) - the beginning and ending of the basic time period.

The trick here is that when the period between C54 and D54 crosses midnight, the
premium time period between them is the total premium time in one day less the
premium time from D54 (the true ending time) to C54 (the true starting time)
treating them as occurring on the same day. Graphically, with | as midnight, @
as 7:00 AM, # as 7:00 PM, + as portions of prime time between C and D, - for
portions of prime time not between C and D, and = for basic time.

C < D (so on same day)

|--C+++D------@=======================#---------|
|--C++++++++++@===D===================#---------|
|--C++++++++++@=======================#++++D----|
|-------------@=C===============D=====#---------|
|-------------@======C================#+++D-----|
|-------------@=======================#-C+++++D-|

C > D (so crossing midnight)

|++D---C++++++@=======================#+++++++++|
|++D----------@===C===================#+++++++++|
|++D----------@=======================#----C++++|
|+++++++++++++@=D===============C=====#+++++++++|
|+++++++++++++@======D================#---C+++++|
|+++++++++++++@=======================#+D-----C+|

If I did this right, it should show that the prime time period between C and D
when it crosses over midnight is the complement of the period on a single day
from D to C. When working with time, it helps to draw timelines.

 

[Sandy Mann]

Dave,

I took a look at your spreadsheet and by the time that I had worked this out
Harlan had posted his solution. Harlan's formula works, (now there's a
surprise! <g>), so this is just to give you an alternative.

The more usual way of calculating elapsed hours (G8) is:
=((D8-C8)+(C8>D8))

the 'IF' in your formula is superfluous but well done for working out the
method yourself. An alternative, which is not so intuitive but which I
prefer is:
=MOD(D8-C8,1)

Instead of your =HOUR(G8)+(MINUTE(G8)/60) in cell H8 which is working with
the calculated time in G8, simply work with the original input cells:
=((D8-C8)+(C8>D8))*24 or =MOD(D8-C8,1)*24 and format the cell as Number. (A
time is a fraction of a day so multiplying a time by 24 gives the hours.)

To split the time into 'Basic' and 'Premium' hours, I would calculate the
Basic with the formula:
=IF(OR(C8<=19/24,D8>7/24),MIN(IF(D8<7/24,19/24,D8),19/24)-MAX(IF(C8>19/24,7/
24,C8),7/24),0)

and then subtract this from the total number of hours:
=G8-L8

to give the Premium hours

None of the above is original to me except the Basic & Premium formula but I
hope that it helps.

Post back if you have any questions

Regards

Sandy

 

[Harlan Grove]

The more usual way of calculating elapsed hours (G8) is:
=((D8-C8)+(C8>D8))

One caveat: if C8 and D8 are equal, does that mean no elapsed time or
exactly a full 24 hours? Your formula assumes no elapsed time.

the 'IF' in your formula is superfluous but well done for working out the
method yourself. An alternative, which is not so intuitive but which I
prefer is:
=MOD(D8-C8,1) ....

Clever.

To split the time into 'Basic' and 'Premium' hours, I would calculate the
Basic with the formula:
=IF(OR(C8<=19/24,D8>7/24),MIN(IF(D8<7/24,19/24,D8),19/24)
-MAX(IF(C8>19/24,7/24,C8),7/24),0)

....

I may not understand this. If time periods given by beginning and ending
times in C8 and D8, respectively, could be any time during the day, then if
C8 were 1:00 AM and D8 were 2:00 AM, your formula gives 0.5, or 12 hours,
even though 1:00 AM to 2:00 AM is entirely outside the 7:00 AM to 7:00 PM
basic time period.

 

[Dave WL]

Thanks very much for this. And I do know the patience of being marrie
- though not of the kids yet - thank heavens. But I digress.

Thanks for this - have put it back into my spreadsheet - unfortunatel
it seems to only like it if all the times cross midnight. MAybe I hav
entered it incorrectly - anyway....

I'll attach a cut down version of the spreadsheet with some exempla
data in it. Most of the times entered are day time (eg. 09:00 t
13:00). It's actually doctor's job hours. 220 diary analyses. Anywa
the reason I was having so much difficulty was those who ended thei
shifts AT midnight.

If you could have a look again I would be eternally grateful.

Dav

Attachment filename: cut down.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39390

 

[Dave WL]

Thanks very much for this. And I do know the patience of being married
- though not of the kids yet - thank heavens. But I digress.

Thanks for this - have put it back into my original spreadsheet -
unfortunately it seems to only like it if all the times cross midnight.
Maybe I have entered it incorrectly - anyway....

There are still some bits that don't seem happy. The (HOUR(D54)+5)
that you mentioned was actually an attempt to get the hours from 7pm on
the day in question to add to the hours from after midnight. This of
course means that IT would only work if the start time was before
19:00.

I'll attach a cut down version of the spreadsheet with some exemplar
data in it. Most of the times entered are day time (eg. 09:00 to
13:00). Unfortunately some do run over midnight from before 19:00 and
some run over midnight from after 19:00 etc etc etc. It's actually
doctor's job hours. 220 diary analyses need to be done ASAP. Anyway
the reason I was having so much difficulty was those who ended their
shifts AT midnight. I was having real difficulty with negative hours -
excel doesn't seem to like them ( and that after adding in
=D8-C8+IF(C8>D8,1) to try to get it to add up over midnight )

Thanks for all you help

If you could have a look again I would be eternally grateful.

Dave

Attachment filename: cut down.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=393915

 

[Dave WL]

Ah, hang on I think I was being particularly thick. I see what yo
meant now. And I have entered it wrong. Thanks for all your help.
will now go off and try to get the wretched thing to work again. O
course I may arse the whole thing up and be back on here in 6 hour
time - begging for an end to my stupidity

Thanks and regards

Dav

 

[Dave WL]

Thanks for this - I'm trying to get the whole sheet working today. I
actually have 220 versions to get it working on so this may take a
while

Anyway thanks again - and you may find me back on here begging for more
help due to extreme idiocy.

Cheers,

Dave

 

[Sandy Mann]

One caveat: if C8 and D8 are equal, does that mean no elapsed time or
exactly a full 24 hours? Your formula assumes no elapsed time.

It is not my formula but yes, as I understand it, to have it return a full
24 hours you need the date included as well.

...

Clever.

Yes again but not my clever. I first saw this formula quoted by Daniel
Maher who said that it came from French NG's.
Myrna said that it works because MOD always returns a posiive number:

*****************************************************************

Yes, the worksheet MOD function always returns a positive number, e.g.

-3 / 2 = -2 with a remainder of +1

That has always seemed strange to me. Not all MOD functions behave like that. The VBA MOD
function does not:

-3 \ 2 = -1 with a remainder of -2.

From what I've read, there are two camps among mathematicians as to what the "right" result is
when the numerator and denominator are of opposite signs.
*******************************************************************
...

I may not understand this. If time periods given by beginning and ending
times in C8 and D8, respectively, could be any time during the day, then if
C8 were 1:00 AM and D8 were 2:00 AM, your formula gives 0.5, or 12 hours,
even though 1:00 AM to 2:00 AM is entirely outside the 7:00 AM to 7:00 PM
basic time period.

Yes you do understand it and do you know how long it took me to get the
formula to return that result? <g>
I tried all sorts of combinations of times before I posted it but obviously
I did not try two times before the start of the Basic hours.
As your formula works and I am not enough of an expert in Excel to have an
ego I will just leave it there.

Happy New Year

Sandy