September 23, 2009

Latest Version Of JMT Excel Utilities

Recently I have made a number of improvements to my utilities. Sorry for not posting sooner!

CHOOSEFROMRANGE and CHOOSEFROMLIST are 2 useful new functions. You can use them to return a random value from a range (for the former) or a custom list (for the latter). I’ve also updated a few of the other functions – special thanks to Jimmy Peña from Code for Excel and Outlook for his help and suggestions.

I have added new features to Insert Sequential List In Selection. You can add, subtract, multiply and divide by a secondary number when inserting Sequential Numbers, Unique Random Numbers and Repeating Random Numbers. You can also change the formatting of the cells for all of the above options as well as Sequential Dates or use the default (not change existing cell formatting). Another feature is that you can start over after inserting a set number of values. After some head scratching, I have decided to move this utility to Text Tools as I felt it fits in better with the other text tools I have on that sub menu.

Finally, something that code dabblers might find handy! You can copy the active cell’s formula to the clipboard for use in VBA, eg ActiveCell.Formula = “”. (Those dreaded double quote marks are replaced with Chr(34)). Choose from A1 or R1C1 conversion, it’s in Developer Tools.

You can download the latest version here :-)

Quick Tip on the Quick Access Toolbar
While I am at it, you can add any tool, sub menu or the entire group to the QAT in Excel 2007 by right clicking it and selecting Add to Quick Access Toolbar.
  

Posted by andrewe at 20:10

September 03, 2009

Days Per Week Between 2 Dates

Out of all the formulas (formulae to the more educated than me) that I work with, I would say that Date formulas are the hardest to get right. Maybe not so surprising considering how weeks don't divide nicely into months. It really is very naughty of them.

Anyway, I put together a formula to work how many days exist in each week between 2 dates. For it to work as expected, the following must be true -

  • The end date must be equal or higher than the start date (D'oh!)

  • The start date must be equal to or higher than the first week's date

  • The end date must be equal to or lower than the last week's date + 6



    Weeks that are not within the date range show as blank. Note the formula also includes the start date as being a day, so if we consider the total days, the calculation would be

    End Date - Start Date + 1 Day

    And the formula? With the Start Date and the End Date being in Cells A2 and B2 respectively, select the appropiate cells starting from Cell C2, enter this formula, then push Ctrl + Enter simultaneously. (Alternatively, enter the formula into Cell C2 and drag to the right)

    =IF(AND(C1>=$A2-6,C1<=$B2),IF(AND($A2-C1>0,$B2<C1+7),$B2-$A2+1,IF(OR($B2=C1,$A2=$B2),1,IF(AND($A2-C1>0,$A2-C1<7),C1+7-$A2,IF(AND($B2-C1>0,$B2-C1<7),$B2-C1+1,7)))),"")

    Can you shorten it? Great if you can, don't hurt your head though :-)

    There is room for improvement though. As is, holidays are not calculated, neither weekends or public holidays. If you come up with a way, (perhaps and another formula or 2 in rows below?), let me know and I will append it to the bottom of this post!

    Update
    Sébastien Labonne of The Financial Modeler has come up with a much shorter formula! (About a third the length of mine and far fewer cell references!)

    =MIN(MAX(0,C1+7-$A2),7)-MIN(MAX(0,C1+6-$B2),7)

    Well done Sébastien! :-)
      
  • Posted by andrewe at 19:18