Couting the Number of Weeks in a Month

I tasked myself with creating a personal budget spreadsheet in Excel. I know there are plenty of them out there, but I could not find one that calculates how many times a weekly or bi-weekly budget item occurs in a given month based on a particular start date. Like some of you I get paid every other week, which means that some months I get paid twice and others I get paid three times (woohoo!). As much fun as it is to figure out how many months I get three paychecks, I don't want to have to manually figure out the number of occurrences for each weekly and bi-weekly line item for each month. That's why computers were invented.

Not finding the exact formula out on the internet to give me what I was looking for (though some were helpful) and still being somewhat of a n00b in Excel, I realized I would have to figure it out using formulas I've never used before but assumed were out there. And I DID NOT want to resort to a VBA user-defined function (UDF); I have been following Chandoo's Excel blog - an unbelievably amazing place to "become awesome in Excel" - and therein have discovered the power of Excel without the need for VBA.

Specifications

• Return the number of weekly or bi-weekly occurrences in a given month
• Based on a Start Date
• Do not use VBA / UDF
• Do not use any other cells to hold portions of the calculation
• Do not use circular references
• Do not have an aneurysm trying to figure this one out

Not wanting to use additional cells to aid with the calculations, I knew the formula would have to involve arrays or something similar. Thankfully, I found out that Excel can do arrays. I also found out how to use the ROW and INDIRECT functions together to create the data to fill said arrays.

Variables

For this example, I copied this formula from cell AW7 of my spreadsheet. It gives me the number of occurrences of my budget line item for January (AW$4).

H is the column that contains the Budget Type:

• W = Weekly - every week based on the Start Date
• BW = Bi-Weekly - every other week based on the Start Date
• SM = Semi-Monthly - twice per month, ignoring the Start Date
• M=Monthly - once per month, ignoring the Start Date

I is the column that contains the Start Date

FiscalYear is the single cell named range that has holds the budget year

4 is the row that contains the month number, one column for each month in columns AW to BH. We will eventually be populating the same number of cells directly below AW4 - BH4 for each line item for which we are creating a budget.

Formula

Here is the formula I came up with:

{=IF(ISBLANK($H7),0,(IF($H7="M",1,(IF($H7="SM",2,SUMPRODUCT(IF(MONTH(ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0))))=AW$4,1,0)*IF(MOD(ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0)))-$I7,IF($H7="BW",14,7))=0,1,0)))))))}

Easy enough, right?

Breakdown

{

Right out of the gate you'll notice the curly braces: this denotes an Array Formula. This lets you do calculations on an array of information in memory without having actual cells for each value on your spreadsheet. When you type an Array Formula you need to press Ctrl-Shift-Enter (CSE) when you are done instead of just hitting Enter; you do not actually type the curly brace - Excel will do this by itself. More on how/why we use the Array Formula later.

=IF(ISBLANK($H7),0,

If our Budget Type cell is blank, return a 0 (zero) - we are not going on a budget for this line item

(IF($H7="M",1,

If our Budget Type is M, return a 1 (one), for once per month.

(IF($H7="SM",2,

If our Budget Type is SM, return a 2 (two), for twice per month.

SUMPRODUCT(

Here, we start our "else" section where the Budget Type is not blank, "M", or "SM"; this leaves "W" and "BW". SUMPRODUCT will give us a sum of all the rows of values in our arrays. We will use two arrays, and it will make more sense when you see what they are.

IF(

Starting with our first array, we are evaluating whether or not the month number returned for each value of the array is part of the month number we are specifying in AW$4.

MONTH(

Get the month number from the date serial number.

ROW(

We need to create an array of numbers to represent a date range. I saw the power of ROW(INDIRECT(...)) here and picked apart Ron Rosenfeld's "very nice" formula (towards the bottom) to see how it worked.

INDIRECT(

The INDIRECT function allows you to specify a range using a non-static value. For example, INDIRECT("1:2") gives us rows 1 and 2. INDIRECT(A1&":"&A2), where A1=5 and A2=10, gives us rows 5 through 10. If you specify a cell containing a date, the serial number for that date is used, and can be converted back to a date as needed.

$I7&":"&

This date range will begin with the Start Date ($I7)...

DATE(FiscalYear,AW$4+1,0)

...and will end on the last day of the specified month (AW$4). The DATE function is in the form DATE(year,month,day). If you notice, our day is 0 (zero); when you use a day of 0, it will give you the last day of the previous month. Hence, we add one (+1) to our month variable AW$4 to get the last day of that month. Don't worry, when we get to month 12, it knows to change month 12+1, day 0 to 12/31.

)))=AW$4,1,0)

We close our INDIRECT, ROW, and MONTH functions and ask if our month is the same as our selected month (AW$4). If it is, return a 1; otherwise, return 0.

*

Multiply our first array, row per row, with our second array. Our first array specifies if the date falls within the selected month (AW$4); our second will let us know if it also falls on a weekly or bi-weekly date, depending on the specified Budget Type ($H7), based on the Start Date ($I7). If both arrays return 1 (1*1=1) we know our budget line includes that date. Conversely, if either or both are 0 (1*0=0 and 0*0=0) our budget line does not include that date.

IF(

We begin our second array. As already mentioned we are finding out which dates fall every 7 or 14 days following the Start Date, depending on whether they are Weekly or Bi-Weekly, respectively.

MOD(

We can easily figure out our 7 or 14 day stepping by whether the remainder of dividing our dates by 7 or 14 is 0 (zero). The MOD function spits out the remainder of two numbers.

ROW(INDIRECT($I7&":"&DATE(FiscalYear,AW$4+1,0)))

This should look familiar. It is exactly the same in the first array so I won't go into another explanation.

-$I7,

We subtract out the start date from each row in the array so we can do our MOD function. For example, if our date range in serial number format is 40,000 through 40,365, subtracting 40,000 gives us 0 through 365. Now we can divide by 7 or 14 and get a proper remainder.

IF($H7="BW",14,7)

Here we find out whether we are dividing by 7 or 14. If our Budget Type ($H7) is "BW", divide by 14; otherwise, divide by 7.

)=0,1,0)))

If our MOD is 0 (zero), meaning that it is evenly divisibly by 7 or 14, return 1; otherwise, return 0.

)

Close out our SUMPRODUCT function.

)))}

Close out our Budget Type IF statements and Array Formula. Don't forget: Excel adds the curly brace when you press CSE - you do not.

Where to go from here

There is plenty of room for improvement in this formula. For instance, the Start Date only works correctly if it is within January; adding a condition looking for a negative number would take care of that. An End Date would also be great. We could add "Bi-Monthly", "Annually" and "Semi-Annually" to the Budget Type list, and base all the Budget Types on the Start Date.

I hope this post not only gets you started with a formula ready to go but also gives you the knowledge to take it a few steps further. Let me know if you found it useful.

Copy Live to Test Company

Note: see updated version here

One of those "busy work" tasks for a Dynamics GP consultant is when we need to create a test company and copy a live database into the test company. Well, I'm not about to automate the creating of the company in GP Utilities, but I can certainly help with the part of backing up and restoring the live database into the test company and running the Microsoft CreateTestCompany.sql script afterwards.
This tried and tested script will perform the following:

• (Optional) Back up the live company database
• (Optional) Back up the test company database
• Restore the live company backup (or, alternately, any specified backup file) over the test company database
• Run Microsoft's CreateTestCompany.sql script against the test company database
• (Optional) Change the posting output options to Screen instead of Printer or File

I find it extremely useful when I need to restore it several times to get a procedure down right.

DECLARE @SourceDatabase varchar(10),
        @TESTDatabase varchar(10),
        @DatabaseFolder varchar(1000),
        @BackupFolder varchar(1000),
        @BackupFilename varchar(100),
        @UseRestoreFile tinyint,
        @RestoreFile varchar(100),
        @TESTBackupFilename varchar(100),
        @BackupTESTFirst tinyint,
        @LogicalFilenameDB varchar(100),
        @LogicalFilenameLog varchar(100),
        @SetPrintToScreen tinyint,
        @SQL varchar(max)
-----------------------------------------------------------------------
-- Set up all the information here for the live and test database names
-----------------------------------------------------------------------

SET        @SourceDatabase =    'TWO'
USE                             [TWO]    -- enter the Source Database between the braces
SET        @TESTDatabase =      'TEST'
SET        @BackupFolder =      'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' --end with a backslash (\)
--                               Folder where you want to save the backup file
SET        @UseRestoreFile =     0    --    0 = Create a backup and restore it
--                                    --    1 = Use the below filename to restore from instead of creating new backup
--                                    Use this when you just want to reload a backup you already created
SET        @RestoreFile =       'TWO_20110111.bak'
SET        @BackupTESTFirst =    1     --    0 = No; 1 = Yes
--                                    Backup the TEST database before restoring
--                                    You should do this the first time you restore per day
SET        @SetPrintToScreen =   1    --    0 = No; 1 = Yes
--                                    Change the posting output to the screen instead of the printer
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT @LogicalFilenameDB =      (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 1
SELECT @LogicalFilenameLog =     (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 0
SELECT @DatabaseFolder =         left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 1
print @LogicalFilenameDB
print @LogicalFilenameLog
print @DatabaseFolder

SET    @SQL = ''

IF @UseRestoreFile = 0
    BEGIN
        SET    @BackupFilename = @BackupFolder + @SourceDatabase +
            '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
            '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
            '_for_' + @TESTDatabase + '.bak'
        END
    ELSE
        BEGIN
            SET @BackupFilename = @BackupFolder + @RestoreFile
        END

print '.'
print 'Backup Filename: ' + @BackupFilename
print '.'

SET @TESTBackupFilename = @BackupFolder + @TESTDatabase +
    '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
    '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
    '_pre_LIVE_restore.bak'

-- check to see if the TEST database is in use first
IF EXISTS(SELECT loginame=rtrim(loginame),hostname,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
        WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
        = @TESTDatabase)
    BEGIN
        PRINT 'The database is in use'
        SELECT loginame=rtrim(loginame),hostname,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
            WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
            = @TESTDatabase
    END
ELSE
    BEGIN
        IF @UseRestoreFile = 0
        BEGIN

        -- Back up the LIVE database
            SET @SQL = '' +
                'PRINT ''Backing up Source database (' + @SourceDatabase + ')''; ' +
                'BACKUP DATABASE [' + @SourceDatabase + '] TO DISK ' +
                '= N''' + @BackupFilename + ''' ' +
                'WITH NOFORMAT, NOINIT, NAME ' +
                '= N''' + @SourceDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
            EXEC (@SQL)
        END

        -- Back up the TEST database
        IF @BackupTESTFirst = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Backing up Destination database (' + @TESTDatabase + ')''; ' +
                    'BACKUP DATABASE [' + @TESTDatabase + '] TO DISK ' +
                    '= N''' + @TESTBackupFilename + ''' ' +
                    'WITH NOFORMAT, NOINIT, NAME ' +
                    '= N''' + @TESTDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                EXEC (@SQL)
            END

        -- Restore to the TEST database
        SET @SQL = '' +
            'PRINT ''Restoring to Destination database (' + @TESTDatabase + ')''; ' +
            'RESTORE DATABASE [' + @TESTDatabase + '] FROM DISK ' +
            '= N''' + @BackupFilename + ''' ' +
            'WITH FILE = 1, ' +
            'MOVE N''' + @LogicalFilenameDB + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Dat.mdf'', ' +
            'MOVE N''' + @LogicalFilenameLog + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Log.ldf'', ' +
            'NOUNLOAD, REPLACE, STATS = 10'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Setting Recovery Model to Simple''; ' +
            'ALTER DATABASE [' + @TESTDatabase + '] SET RECOVERY SIMPLE WITH NO_WAIT'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Shrinking Log File'' ' +
            'USE [' + @TESTDatabase + '] ' +
            'DBCC SHRINKFILE (N''' + @LogicalFilenameLog + ''' , 0, TRUNCATEONLY)'
        EXEC (@SQL)

        IF @SetPrintToScreen = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Setting output to print to screen instead of printer'' ' +
                    'USE [' + @TESTDatabase + '] ' +
                    'UPDATE SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0'
                EXEC (@SQL)
            END

        -- Run the CreateTestCompany script from MS
        SET @SQL = '' +
            'PRINT ''Running CreateTestCompany script on ' + @TESTDatabase + '''; ' +
            'USE [' + @TESTDatabase + '] ' +
            'if not exists(select 1 from tempdb.dbo.sysobjects where name = ''##updatedTables'') ' +
            ' create table [##updatedTables] ([tableName] char(100)) ' +
            'truncate table ##updatedTables ' +
            'declare @cStatement varchar(255) ' +
            'declare G_cursor CURSOR for ' +
            'select ' +
            'case ' +
            'when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'') ' +
            ' then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) ' +
            'else ' +
            '''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' ' +
            'end ' +
            'from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c ' +
            'where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'', ''COMPANYCODE_I'') ' +
            'and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ''BASE TABLE''; ' +
            'set nocount on; ' +
            'OPEN G_cursor; ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'WHILE (@@FETCH_STATUS <> -1) ' +
            'begin ' +
            'insert ##updatedTables select ' +
            'substring(@cStatement,8,patindex(''%set%'',@cStatement)-9) ' +
            'Exec (@cStatement) ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'end ' +
            'DEALLOCATE G_cursor ' +
            'select [tableName] as ''Tables that were Updated'' from ##updatedTables '
        EXEC (@SQL)
            PRINT 'Don''t forget to run GP SQL Maintenance on the ' + @TESTDatabase + ' database.'
    END

Add-In Initialization Error

Got this error today at one of our customers when opening Dynamics GP 10:

Microsoft.Dynamics.GP.BusinessIntelligence.DeployDataConnections.dll:
Could not load file or assembly 'Application.Dynamics, Version=10.0.0.0,
Culture=neutral, PublicKeyToken=... or one of its
dependencies. Insufficient system resources exist to complete the requested
service. (Exception from HRESULT: 0x800705AA) InnerException:
Insufficient system resources exist to complete the requested service.
(Exception from HRESULT: 0x800705AA)

Windows Server 2008 32-bit, Remote Desktop server

I checked the obvious, given "insufficient system resources":

Available RAM: Gigs free
Available Hard Disk: hundreds of Gigs free
Um... CPU usage..? less than 5% usage

Before trying to repair the installation of Dynamics GP I figured I would reboot. I've ignored that underestimatedly valid option too many times lately to blow it off once again. Fortunately, it paid off and they were able to get back in.

If anyone runs into this and solves it with something other than a reboot, a little feedback would be nice. I'm not discounting the fact that I may run into it again sometime soon at this customer.

Match SQL Access to GP Access

We recently reformatted an 80-company Dynamics GP installation using Corporate Renassaince Group's Reformatter. The Reformatter migrates to a second SQL server, data is bulk copied over, and the accounts are re-formatted in the process. Works great. But...

Two things you have to do yourself: set up companies in GP Utilities on the new server for each company in your installation, and set up SQL logins and give them access to each database in SQL. I used the KB878449_Capture_Logins.sql script and reset the user passwords, but I still had to manually add them to the same SQL databases they had on the existing server. Imagine doing that for 10 users times 80 companies!

Well, that's why I created the script below, but it has other applications as well. Have you ever created a test company, copied the live data over, went into GP's User Access window, and checked the Access box for that new company only to get "The user could not be added to one or more databases"? This script is for you. It will also remove users from databases to which they should not have access.

Other errors this script may fix (depending on the root cause) include "A get/change operation on table 'syUserDefaults' failed accessing SQL data.", "The server principal (login) is not able to access the database (database) under the current security context.", and "A get/change first operation on table 'coProcess' failed accessing SQL data."

/*
This script will set access to SQL databases where each GP user has access
  within the User Access window inside GP.
You can run this after setting up a test company, or any time you get the error:
  The user could not be added to one or more databases.
*/
declare @DatabaseId    varchar(5),
        @UserId        varchar(30),
        @NotFound      tinyint,
        @SQL           varchar(max)
set     @SQL = ''
declare UserLoop cursor for
    select USERID from DYNAMICS..SY01400
        where USERID NOT IN ('sa','DYNSA')
open UserLoop
fetch next from UserLoop into @UserId
while (@@fetch_status <> -1)
    begin
        SET @SQL = @SQL + 'USE [DYNAMICS]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    BEGIN
        CREATE USER [' + @UserId + '] FOR LOGIN [' + @UserId + ']
        EXEC sp_addrolemember N''DYNGRP'', N''' + @UserId + '''
    END
'
    fetch next from UserLoop into @UserId
    end
close UserLoop
deallocate UserLoop
declare DatabaseLoop cursor for
    select RTRIM(c.INTERID),RTRIM(u.USERID),(CASE WHEN a.USERID IS NULL THEN 1 ELSE 0 END) NotFound
    from DYNAMICS..SY01500 c cross join DYNAMICS..SY01400 u left join DYNAMICS..SY60100 a ON c.CMPANYID = a.CMPANYID AND u.USERID = a.USERID
    WHERE INTERID IN
        (select name from master..sysdatabases)
    AND u.USERID NOT IN ('sa','DYNSA')
open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId,@UserId,@NotFound
while (@@fetch_status <> -1)
    begin
    /*Inner Loop for each Database*/
        IF @NotFound = 0 -- it is found; add it if it does not exist
            SET @SQL = @SQL +    'USE [' + @DatabaseId + ']
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    BEGIN
        CREATE USER [' + @UserId + '] FOR LOGIN [' + @UserId + ']
        EXEC sp_addrolemember N''DYNGRP'', N''' + @UserId + '''
    END
'
        ELSE
            SET @SQL = @SQL +    'USE [' + @DatabaseId + ']
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @UserId + ''')
    DROP USER [' + @UserId + ']
'
    fetch next from DatabaseLoop into @DatabaseId, @UserId, @NotFound
    end
close DatabaseLoop
deallocate DatabaseLoop
exec (@SQL)