Be Excellent To Each Other

And, you know, party on. Dude.
It is currently Wed Aug 16, 2017 14:36

All times are UTC [ DST ]




Reply to topic  [ 75 posts ]  Go to page Previous  1, 2, 3
Author Message
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 16:44 
User avatar

Joined: 12th Apr, 2008
Posts: 11902
Location: Oxford
Joans wrote:
I did say it wasn't elegant.


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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 17:01 
User avatar

Joined: 31st Mar, 2008
Posts: 7852
Kern wrote:
Joans wrote:
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 18:08 
User avatar

Joined: 12th Apr, 2008
Posts: 11902
Location: Oxford
It's character-building.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 02, 2017 19:18 
User avatar

Joined: 31st Mar, 2008
Posts: 7852
Take out a bracket.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 6:23 
User avatar

Joined: 31st Mar, 2008
Posts: 7852
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:

Code:
08:00   16:59        £83.00  14:30   17:30    14:30   16:59
17:00   21:59:59   £95.00                          17:00   17:30
22:00   23:59:59   £23.00                          0          0
22:00   23:59:59   £23.00                          0          0


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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 8:28 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11390
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."

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:02 
User avatar

Joined: 31st Mar, 2008
Posts: 7852
Oh, what version of excel is it, maybe they've changed a limit. Or it needs to be an xlsx file, rather than xls.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:30 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11390
Well, the formula does something :)

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:31 
User avatar

Joined: 31st Mar, 2008
Posts: 7852
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


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:36 
User avatar

Joined: 31st Mar, 2008
Posts: 7852
Bobbyaro wrote:
Well, the formula does something :)


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

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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:42 
SupaMod
User avatar
"Praisebot"

Joined: 30th Mar, 2008
Posts: 13358
Location: Parts unknown
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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 9:44 

Joined: 27th Mar, 2008
Posts: 18273
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.

_________________
ImageImage


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 11:06 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 1366
Joans wrote:
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


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.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 11:12 
User avatar
Heavy Metal Tough Guy

Joined: 31st Mar, 2008
Posts: 4996
Probably! There are serious Excel competitions, certainly.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu Aug 03, 2017 11:20 
User avatar
Prince of Fops

Joined: 14th May, 2009
Posts: 1366
Squirt wrote:
Probably! There are serious Excel competitions, certainly.


Yowzers.

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


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 75 posts ]  Go to page Previous  1, 2, 3

All times are UTC [ DST ]


Who is online

Users browsing this forum: Columbo, MaliA, markg, Mimi, Satsuma, Vogons and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.