0

clockAnyone get that song reference? I give you a clue down the bottom of the post…

Well have to say I do love my students (in a good way…not a weird way I hasten to add. :-).). I recently had a woman in class who had the following Excel conundrum…

  1. She needed to calculate the hours worked by staff members…so she had their clock in time/clock out time.
  2. I’m not sure if this was the case but what I’ve done in this example is assume that the staff members clocked in and out for lunch.
  3. So she needed to be able to calculate their hours worked but rounded up to the next 15 minutes e.g. if someone worked 10 hours and 10 minutes, that had to be rounded up to 10 hours and 15 minutes for payment purposes.
  1. But what I did was enter the following formula…after I had formatted the times to HH:MM (see Format Cells – (use Ctrl and 1 – Custom)
  2. =(C3-B3)*24-(E3-D3)*24
  3. That gave me the total number of hours/minutes used in decimal format.
  4. However, I then combined this with the Ceiling function to round up to the next 15 minutes (.25 of an hour
  5. You can download the file here

 

PS you can find out about the lyrics of the song here..

Continue Reading

0

Not so long ago I was asked by a user for help with her file. She wanted people to be able to sort and filter but not do ANYTHING else with it. Of course I went to try my usual things of setting up protection

Review – Protect Sheet – tick Sort, tick Autofilter.

Much to my surprise, that didn’t work. So after some rapid fire googling and some playing around, I eventually came up with this…

  1. Highlight the area you want users to be able to sort and filter
  2. Make sure the Filter is turned on (Data – Filter)
  3. From Review – Allow users to Edit Ranges
  4. Don’t use a password at this point (otherwise your users will have to use a password even to sort and filter)
  5. Click New
  6. The range you have selected should appear in the dialog box. You can give it a name at this point if you wish or just accept the default.
  7. Click OK.
  8. Then click Apply and OK

Whew, once you have done all that, you still have to apply sheet protection.

  1. Go to Review – Protect Sheet
  2. Choose what you want people to be able to do i.e. tick the Sort box and the Autofilter box.
  3. Make sure the Select Locked Cells and Select Unlocked Cells are NOT ticked.
  4. Enter your password
  5. You will then be prompted to re-enter it.

Note that for sorting you will have to use the Data – Sort dialog box to sort the data. The filter should already be turned on and users without the password should be able to sort and filter but NOT change anything.

Now your users should be able to sort and filter (but not do anything else) . As always your questions and comments are most welcome.

 

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper. 

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only

 

 

 

 

Continue Reading

0

I was recently teaching a class on Powerpivot and the subject came up of how to use Excel to construct an aging analysis. The person had already created some serious serious formulas to do what he needed to do so no point in re-inventing the wheel. His big interest was how to do all that in Powerpivot.

To that end, I suggested he have a look at this article…

But his question got me thinking…how would I do that 

So this is what I came up with. You can download the file here: Aging debtors’ analysis. I have to say for me I like to break these formulas down into separate segments ( they are easier to trouble shoot that way…)

  1. Add an extra field to my invoices to generate the number of days outstanding (=TODAY()-A3 (or invoice date) 
  2. Create a vlookup table (see Debtor Lookup) which allocates a specific status to days. As you will see, I watch way too many Mafia programmes…. :-) Important point to note about that is that Range Lookup should be left blank or enter true as you are referencing a range of days…not a specific number of days.
  3. Using this vlookup table, create a vlookup formula  that returns the appropriate status for each invoice.
  4. The aging analysis sheet uses pivot tables (here is the first tutorial in my pivot table series) to generate totals. Note that I manually re-ordered the listings to give the most logical one i.e. Current at the top.
  5. Obviously as the days and invoice mount up, all you need to do is click on Refresh (Options – Refresh) in the pivot table to give you the most up to date profile.

Excel Products for Sale

Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.

MrExcel.com

Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!)  that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.

MyExcelOnline.com

Xtreme Pivot Tables course

This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.

He also has a useful Pivot Table webinar…

This is also a useful chart helper. 

Debra Dalgleish – Contextures.com

Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.

Excel UserForms for Data Entry

The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.

PivotPowerPremiumRibbon

The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function

Chandoo.org

Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only

 

 

 

 

Continue Reading