Be Excellent To Each Other

Are there dates, too? Some of those times have a later start then end, suggesting they've rolled over midnight

Oh, that's not such a hardship. Just do an if to see if one is bigger than the other and take the evening start hour away from the morning end hour plus 24.

Except it could be 8 days later, there's no way to tell

Valid.

I've just actually realised what Bobby is fuckering on about. Those time slots aren't the start and end times, they're rates. So if you work three hours between 7pm and 7am, the rate is X. I'll have another look when all the Zeds have been dealt with.

Yes, there are three time slots, in the list, these assign a cost/hour to any work done. The work done can span these time slots and needs to be divided accordingly. So if I start work at 16:50 and finish at 17:20 I have 10 minutes at rate 1 and 20 at rate 2.

It is unlikely they would span more than 3 shifts, though.

Why are there two rows for 22:00 to 7:59:59?

We'll, that's the next step, that is the weekend rate! Ignore that for now.

Elegant this is not.
Assuming the stuff you posted on the previous page is in cells ("A1:C3"), put your start and end times in D1 and E1, then put this monstrosity into E1 and copy down for each shift range (i.e. Rows 1-3). This should give you the total rate for each range (I assumed it was an hourly rate, and rounded to the nearest 15 minutes).
=(ROUND(MINUTE(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))/15, 0)/4*C1)+(HOUR(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))*C1)

Actually, ignore that, it doesn't work if you span multiple shifts and days, 14.30 - 17.30 works, 23.00 - 5.00 works, but 21.00 - 05.00 doesn't.

Does this have to be done in Excel?

It looks amazing though, Joans

I'd be thinking something other than excel would be preferable for this. Or VBA sitting behind excel would certainly do it. I think maybe one approach would be to split the time you worked into 30-minute chunks, and for each chunk check which bracket it's in and apply (half) the appropriate rate.

Would this work?

Create a static table, start and end times along x and y, and fill in the calculated values. Then use index() and match () to look up from the table.

Wouldn't you spend about the next 15 years working out what all the calculated values would need to be to fill in the table?

Yeah even VBA would be helpful, however I work with what I've got. So, try this...

=(ROUND(MINUTE((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))/15, 0)/4*C1)+(HOUR((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))*C1)

And people say Perl is unreadable.

I'm sure a formula could be worked out to do most of it.

In fairness it's because I had about 7 calculated fields, which I combined into one user-friendly formula. If I did it all separately it wouldn't be that bad, but where's the fun in that?

Wow! Joans, am of work today, week try it tomorrow, but seriously, thanks for the effort!

It still doesn't quite work, sorry, something like 21:00 - 09:00 sends it a bit funny.

Right, final attempt:

=((ROUND(MINUTE((IF((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1)))<0, ((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))+1)-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))), (IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))))))/15, 0)/4)*C1)+(HOUR((IF((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1)))<0, ((IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))+1)-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))), (IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1)))-(IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))))))*C1)

Holy shit

Is Joans trying to hack the forum?

You sunk my Battleship.

The elegant way seems to be to get Joans to come up with all the IF statements

I'm pretty sure that's Inception in an Excel Formula. In the middle of all those nested IF statements time is moving incredibly slowly.

The system goes online August 2nd, 2017. Human decisions are removed from billing. Joans' Formula begins to learn at a geometric rate. It becomes self-aware at 2:14 a.m. Eastern time, August 29th. In a panic, we try to pull the plug.

... IFnet fights back.

I did say it wasn't elegant.

I might ask my apprentice to properly parse it tomorrow as part of his Excel training.

I take it you don't like him.

It's character-building.

Take out a bracket.

Actual elegant(ish) solution update.

Split the 22:00 - 07:59:59 range into two rows - i.e. 22:00 - 23:59:59 and 00:00 - 07:59:59, now column B is always greater than column A.
Include the date with the actual start and end times, so in the event of a shift going over midnight, E1 will still be greater than D1. You'll presumably need to do this anyway if part 2 involves weekends as you'll need to know which day of the week you're dealing with.

Now you can just repeat what I did yesterday, which was basically in columns F & G, work out what the effective start and end date is for each band. Because you now don't have to consider B possibly being greater than A, or E1 possibly being greater than D1 (or both), this should be a lot more straightforward.
So, the 14:30 - 17:30 example would return:



Then you can work out how long is in each period, how many hours and multiply that by the rate (I'm assuming it's an hourly rate) and do whatever you want to do with the minutes?

Obviously, based on the times you've provided, someone working 14:30 - 17:30 is only getting paid for 2 hours and 59 minutes, because 16:59 - 17:00 doesn't fit in any of those bands. Similarly, for the other bands, there's a second that's missing, so it might make sense to change each band so the end time is the same as the next start time (although leave 23:59:59 as is, otherwise you end up with a value in B less than the corresponding value in A, so you might want to fudge that extra second in somewhere later in the process, and if you're doing that, you might as well leave the other end times as is (apart from 16:59) and fix it for all of them.

Hopefully that makes it a bit more straightforward, but if I get bored at work again, I'll probably do a bit more.

You are a lunatic!

Alos, I get the error message "The specified formula cannot be entered because it contains more value, references and/or names than are allowed in the current file format."

Oh, what version of excel is it, maybe they've changed a limit. Or it needs to be an xlsx file, rather than xls.

Well, the formula does something

You’re probably better off doing it like this then, putting each part of the formula into its own column, the monstrosity I posted yesterday was just column N, but replacing each cell reference all the way back so it appeared in a single cell. This should be a bit more legible, and you’ll probably be able to work it out a bit better if anything is going wrong. Column H onwards you should be able to work out if you want it set up any differently.

Column F - the earliest relevant time in that range, i.e. either A1 or D1, assuming the range is relevant at all.
=IF(OR(AND(A1<B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1>B1, D$1<E$1, D$1<A1, E$1>A1), AND(A1<B1, D$1>E$1, E$1>A1, E$1<B1), AND(A1>B1, D$1>E$1, D$1<A1)), A1, IF(OR(AND(A1<B1, D$1<E$1, D$1>A1, D$1<B1), AND(A1>B1, D$1>E$1, D$1>A1), AND(A1>B1, D$1<E$1, D$1<B1), AND(A1<B1, D$1>E$1, A1<D$1, B1>D$1), AND(A1>B1, D$1<E$1, D$1>A1)), D$1))

Column G - the latest relevant time in the range)
=IF(OR(AND(A1<B1,D$1<E$1,D$1<B1,E$1>B1),AND(A1>B1,D$1<E$1,D$1<B1,E$1>B1), AND(A1<B1, D$1>E$1, D$1<B1, D$1>A1), AND(A1>B1, D$1>E$1, B1<E$1)),B1,IF(OR(AND(A1<B1,D$1<E$1,E$1>A1,E$1<B1), AND(A1>B1, D$1>E$1, E$1<B1), AND(A1<B1, D$1>E$1, E$1>A1), AND(A1>B1, D$1<E$1, E$1>A1)),E$1))

Column H – The amount of time in that range applicable to this shift
=IF(G1-F1<0, (G1+1)-F1, G1-F1)

Column I - Complete hours in the range
=HOUR(H1)

Column J – minutes in the range
=MINUTE(H1)

Column K – rate based on hours
=I1*C1

Column L – number of minutes in the range, converted into hours (to the nearest quarter hour – e.g. 30 minutes = 0.5 hours). You probably don’t want this to work like that, but I fudged it in to deal with the missing minutes/seconds between the shift ranges you’ve defined.
=ROUND(J1/15, 0)/4

Column M – rate based on minutes
=L1*C1

Column N – total rate
=K1 + M1

In theory, it gives you the total rate for each period, but I never actually tested it.

The start and end times looked right though, and with my post above you should be able to sort out what to do from there if there is something else wrong.

If I were you Bobby, I'd give up on this completely and start putting together a spreadsheet of all the games you own instead. That's much easier.

Joans has written 99.5% of the solution. He'll reveal the final piece only at the cottage weekend.

I don't know why I want you to go so much. I'm not even going to be there.

Just doesn't seem right.

Genuine question. Would there be demand for a book of Excel puzzles, teasers and conundrums? Like the sort of compendium you get for Sudoku.

Great for train journeys and stocking fillers for your nerdier friends.

Probably! There are serious Excel competitions, certainly.

Yowzers.

https://techcommunity.microsoft.com/t5/Excel-World-Champ/ExcelWorldChamp-Awards-Show/td-p/55315

Man, I am terrible at Excel. I have Sheet A and Sheet B. Sheet A has a column with some numbers in it. I want a field in Sheet B to tell me how many of these fields have the number 1 in it, one to tell me how many are 1 to 5, and another to tell me how many are 1 to 10.

I tried this but it doesn't work:



Help me, excellent friends.

You'll want the COUNTIF function I think.

I can't seem to get that function working at all.

so assuming you're looking for '1' in the A column

Ok great. What about for a range of numbers like 1-5 or 1-10? I tried this but it doesn't work: