Excel Question.

[Sluggo]

A question for the Excel experts out there.

I'm using Excel 2007 to add together a collection of times in hh:mm:ss format. I've set the 'total' column to time format but the problem is that it resets at 24 hours and goes back to 0. For my purposes, more the 24 hours is a valid time. Anyone know how I can stop the 'total' column from resetting and display 25:37:04 ... for instance.

Rep and a virtual pint (or celebratory beverager of your choice) for the first answer that works!

[Barbara4003]

Not sure if this will help. It doesn't say which version of Excel this applies to, but it may work for all versions

http://www.ozgrid.com/Excel/times-past-24.htm

I'm not sure if that's what you mean though

[Palestinian]

Select the cell(s) you want the format
Right mouse click ‘format cells’
Select number, then custom
Scroll down until you find the option [h]:mm:ss
Select
Click OK

[Sluggo]

You must spread some Reputation around before giving it to Barbara4003 again.

Took a bit of following, but yes it was what I was looking for.

Also rep for Palestinian for the clear, simple instructions.

Virtual drinks on the way.

[Sluggo]

Been to the bar ...

A pint for Palestinian ...

[Sluggo]

And for Barbara, I wasn't sure what to get but this looked interesting.

[Mr Statto]

Damn, too slow. A virtual pint would have gone down nicely!

[Barbara4003]

Originally Posted by Sluggo

And for Barbara, I wasn't sure what to get but this looked interesting.

That does look interesting. Thanks

[Mr Statto]

Originally Posted by Palestinian

Select the cell(s) you want the format
Right mouse click ‘format cells’
Select number, then custom
Scroll down until you find the option [h]:mm:ss
Select
Click OK

Or if you can't be bothered to scroll down, just type it in the box

[Palestinian]

Originally Posted by Sluggo

Been to the bar ...

A pint for Palestinian ...

cheers - although you look as though you've attempted to give Barbara some evil potion

[stinky]

Excel formulae are the work of the devil

Where's my pint?

[jimos_uk]

Rather than start a new thread, I thought i'd give this a little bump.

I'm trying to put together a spreadsheet which details commission payments - only problem is, it's based on a hockey-stick type nightmare.

Basically, as an example, if your target is 100,000, you'd get 1% for the first 80,000, then 2% for the next 20,000, then 3% for the next 15,000 and so on.

I am really not sure how to reflect this in an excel formula, what with them being the work of the devil. Every time I try to do this, it seems to just take the whole value (so, at 90,000, it counts everything at 2%).

Anyone got any suggestions?

Thanks!

[Mr Statto]

Originally Posted by jimos_uk

Rather than start a new thread, I thought i'd give this a little bump.

I'm trying to put together a spreadsheet which details commission payments - only problem is, it's based on a hockey-stick type nightmare.

Basically, as an example, if your target is 100,000, you'd get 1% for the first 80,000, then 2% for the next 20,000, then 3% for the next 15,000 and so on.

I am really not sure how to reflect this in an excel formula, what with them being the work of the devil. Every time I try to do this, it seems to just take the whole value (so, at 90,000, it counts everything at 2%).

Anyone got any suggestions?

Thanks!

Just do it as a sum of different bits - something like = (80000 * 0.01) + (20000 * 0.02) + (15000 * 0.03) etc. If it depends on the amount then you might need to include some if clauses - eg = if (A1< 80000,A1 * 0.01, (if(A1<100000, (80000 * 0.01) + ((a1-80000)*0.02) etc

If you're stuggling, let me know the actual bands & I'll whip up a formula for you

[jimos_uk]

The brilliant Mr Statto saves the day.

Thanks again!

[Baldy]

I have an Excel question.

How can I retrieve a password from a protected document without downloading any software from the internet ?

If anyone can help me it is worth serious beers......

[renzo]

Sorry the only way I know to remove/change a password is to use an downloaded editor. The one i tend to use is hex editor neo

[Baldy]

Originally Posted by renzo

Sorry the only way I know to remove/change a password is to use an downloaded editor. The one i tend to use is hex editor neo

Ok, I have downloaded it and opened the excel file in the editor. Where/how do i see the protected password ??

[renzo]

Close the workbook and open the workbook file in the hex editor. Find the string "DPB" and change it to "DPx". Save the file. Open the workbook and click OK until the workbook is open (one or more dialogs are displayed describing various problems with the VBA project). Press ALT+F11, choose the menu command Tools->VBAProject Properties, navigate to the Protection tab, and change the password but do not remove it (note the new password). Save, close, and re-open the workbook. Press ALT+F11 and enter the new password. Choose Tools->VBAProject Properties, navigate to the Protection tab, and remove the password. Save the workbook.

[Baldy]

Originally Posted by renzo

Close the workbook and open the workbook file in the hex editor. Find the string "DPB" and change it to "DPx". Save the file. Open the workbook and click OK until the workbook is open (one or more dialogs are displayed describing various problems with the VBA project). Press ALT+F11, choose the menu command Tools->VBAProject Properties, navigate to the Protection tab, and change the password but do not remove it (note the new password). Save, close, and re-open the workbook. Press ALT+F11 and enter the new password. Choose Tools->VBAProject Properties, navigate to the Protection tab, and remove the password. Save the workbook.

Thanks, but this works but doesnt show me the original password which is what I need.

How can I locate the original password ?

[renzo]

Not sure how to display what the original password was, surely resetting the password sorts out your issues