Project Date Calculations in Excel

A relatively common demand management question I’ve been asked to solve in Excel numerous times is how do I figure out how much time is a person allocated to a project during a certain month?

The answer is relatively simple but requires a visual aid to help figure out the logic of the different scenarios:

As you can see – there are 4 cases that can occur. Our goal is to write an IF statement that models all of these scenarios.

Ta Da! Here you go:

=IF(AND([Project Start]<[Month Start],[Project End]>[Month End]),1,
IF(AND([Project Start]<[Month Start],[Project End]<=[Month End],[Project End]>=[Month Start]),(NETWORKDAYS([Month Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]>[Month End]),(NETWORKDAYS([Project Start],[Month End])/NETWORKDAYS([Month Start],[Month End])),
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]<=[Month End]),(NETWORKDAYS([Project Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),0))))

Let's break this down line by line.

=IF(AND([Project Start]<[Month Start],[Project End]>[Month End]),1,

translated is case #1 and it would give us a full 100% allocation during that month.

IF(AND([Project Start]<[Month Start],[Project End]<=[Month End],[Project End]>=[Month Start]),(NETWORKDAYS([Month Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),

translated is case #2 (Project End is either before or equal to Month End), we then find the number of work days between month start & project end and then divide by the number of work days in the month to find the percentage of allocated time.

IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]>[Month End]),(NETWORKDAYS([Project Start],[Month End])/NETWORKDAYS([Month Start],[Month End])),

translated is case #4 (Project Start is after Month Start and before Month End, find the number of work days between Project Start and Month End divided by the total number of work days in the month.)

IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]<=[Month End]),(NETWORKDAYS([Project Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),0))))

translated is case #3 (Project Start is after Month Start and before Month End and Project End is before Month End, find the number of work days between Project Start and Project End divided by the total number of work days in the month.)

Using this formula, you can find (per month, or any other given date range such as an entire year) what percentage of time the project takes up within that date range. This percentage can then be used to calculate number of hours, cost of project, etc.

If you have a specific use case you are interested in getting help with let me know!

~Yosef

Can’t remember your PIN? Here are some tips for choosing a new one!

In today’s era of computers, the numerical password or PIN is incredibly easy for a computer to guess. That being said, here are some ideas to make the number you choose harder to crack and easier for you to remember.

First off, a computer can guess any numerical PIN less than 11 digits long in about 2 seconds thanks to the power of iterative guessing.
The best way to protect your PIN is to enable a time out on whatever device you have. For example, on your phone or tablet, if someone enters the wrong PIN more than X number of times, it wipes the device’s data, or it makes you wait a minute between each try, etc.

Next, try using the following methods to pick a PIN that’s difficult (for a human) to guess, but that you can remember:

• Make your PIN as long as you can. This will depend upon your device & your memorization abilities. Assuming you can use all 10 digits (0 – 9), a 4 digit PIN gives you 10^4 or 10,000 possible combinations. 5 digits gives you 100,000, etc. The longer the PIN, the harder to guess.
• Don’t use a number someone else would know (such as your birthday).
• Use the keypad on your phone to spell a random word that you will remember. E.g. NAME = 6263).
• Make a sentence out of numbers. E.g. using the first letters of each word in “This Sentence Is Awesome!” gives a PIN (on a phone keypad) of 8742.
• “Encrypt” your password by appending another number to it. E.g. if your birthday is July 19th, choose a PIN of 0719 and then tack on another number (such as the last 4 of your phone number) to give you: 07191234
• Don’t use a common PIN – 10% of PINs equal 1234, another 10% are 0000 and 1111. That means that 20% of the time, I can guess your PIN using those 3 numbers. A number like 2580 looks random but it’s the 22nd most common PIN. Why? Because it’s straight up & down on a phone keypad. Be unique – don’t follow the herd.
• Use sports players jersey numbers. This doesn’t work for me because I’m not a sports guy, but for those of you who are – just concatenate the jersey numbers of your favorite players together. E.g. Babe Ruth (3) + Lou Gehrig (4) + Yogi Berra (8) could give you a PIN of 030408.
• Choose a number you like such as your birthday and then subtract or add another number you like. So your birth year might be 1955 and your wedding year might be 1978. The difference is 23. Subtract 23 from your birth year to get 1932. To make it even harder to guess, reverse the numbers to get 2391.
• For a bank card, use the assigned random PIN and memorize it.

Helpful? Got tips or tricks of your own for remembering PINs? Share in the comments below!

~Yosef

Microsoft Task Scheduler Tips & Tricks

I use Microsoft Task Scheduler for a couple of different jobs and over time I’ve found that different jobs require different “tricks” to get them to work properly. I’m going to try to summarize some of those tips & tricks here for you.

First though, I’m going to quickly sketch out how to create a simple task in Task Scheduler. If you are already comfortable with creating tasks feel free to skip to the Tips & Tricks section below.

As an example task – I will walk you through creating a scheduled pop-up reminder in Task Scheduler.

1. In Windows 7 or Windows 8, press the Windows key to bring up your start menu/page and type “Task Scheduler”
2. Open the program with that name
3. On the right hand side of the window, click the “Create Task” button in the “Actions” list
4. Type a name for your task in the “Name” field
5. In the “Configure for:” drop down menu at the bottom of the screen, choose the operating system that you are using (for some reason on Windows 7, this menu defaults to Vista.)
6. Select the “Triggers” tab along the top of the window
7. Click the “New…” button
8. Select from the various options what type of schedule you would like your task to run on. For this example, I have chosen to begin the task on a schedule, Daily, Repeat task every 30 minutes for a duration of “Indefinitely”
9. Click the “OK” button
10. Select the “Actions” tab along the top of the window
11. Click the “New” button
12. From the “Action” pull down menu, I have selected “Display a message”
13. Title: Reminder!
14. Message: Stand up & stretch!
15. Click the “OK” button
16. Select the “Conditions” tab along the top of the window
17. Depending on the task, I usually deselect the “Stop if the computer switches to battery power” and “Start the task only if the computer is on AC power” options.
18. Select the “Settings” tab along the top of the window
19. Select the “Run task as soon as possible after a scheduled start is missed”
20. Change the “Stop the task if it runs longer than:” option to 1 hour. This will keep a task from running in the background chewing up processing power if it fails to exit cleanly
21. Click “OK”

You now have a basic reminder set to go off every 30 minutes to help you keep your blood flowing while you read my long tutorials 😉

Tips & Tricks

Okay, now for some more detailed Tips & Tricks.

1. On system start-up – if your task calls a batch job, and keeps failing to execute, you may need to grant your user(s) the “Logon as a batch job” permission
   1. To do so: In the Control Panel, open Administrative Tools, then Local Security Policy (Or simply type “Local Security Policy” in your start menu)
   2. Beneath Security Settings, open Local Policies and highlight User Rights Assignment
   3. Locate Log on as a batch job. Open the properties and add any users that need this permission
   4. When finished, save your changes and close the Local Security Settings window
   5. Your changes should take effect immediately. To make changes to the Domain Security Policy, on a domain controller, use the Domain Security Policy utility in the Control Panel
2. Instead of choosing to start a task when the system starts, choose “At logon” with the check-box for “Any user” checked. Sometimes a program will behave better if you start it at log on instead of at system start-up.
3. If you have a lot of programs that start at system start-up or user log on, you may want to stagger your tasks to start over 30 seconds or a minute after logon to try to give the computer a break between tasks. This can help keep your computer from freezing up, trying to start so many programs at once.
4. Try experimenting with calling batch files on a scheduled basis. You can automate many different chores this way. Examples that I run including automating file backups, automating e-mail backups, reminders to stand & stretch, automatically start programs at logon, deleting temporary file directories on a regular basis, etc.

Outlook Quick Steps – Improve your workflow

Quick Steps are little automated actions that you can set up in Outlook to improve your workflow. Examples of Quick Steps include:

• Filing steps (moving, copying, deleting)
• Change message status (read, unread, important)
• Categorize, add to tasks & set flags
• Hotkeys for response actions (reply, forward, etc.)
• Hotkeys for creating new appointments
• Hotkeys for dealing with conversation threads

In addition, you can combine actions.
For example, you can set up a hotkey which sends an e-mail to a specified folder, sets the status to important and starts a new appointment dialog window all with one key.

1. To create a new Quick Step, select the “Create New” button in the “Quick Steps” section on the “Home” tab on the ribbon.



2. Enter a name for this new shortcut in the “Name:” box.
3. Pull down on the “Actions” drop down menu and select (for example) “Move to folder”.
4. In the new pull down menu below, choose which folder you want to move the mail to.
5. Select the “Add Action” button if you want to add a secondary action. Repeat steps 3 and 4 for the new action.
6. Choose a shortcut (hotkey) combination if desired.
7. Click “Finish”.

Expanding the Windows 7 Taskbar Icons

Many people are now using Windows 7 and don’t realize how customizable it is.

One simple example is how to expand the Task Bar icons so that you don’t have to hover over each icon trying to figure out which Excel or Firefox window you want to open.

To adjust your Windows 7 Task Bar settings, right-click on an empty section of the bar and select “Properties”. In the window that opens, there should be 3 tabs with the default tab “Taskbar” selected. In the bottom of the first section of the Taskbar tab entitled “Taskbar appearance” there is a drop down menu entitled “Taskbar buttons:”.

The drop down has three options:

1. Always combine, hide labels – this is the default option that only shows tiles for each program along the taskbar



2. Combine when taskbar is full – this option will begin to combine programs into tiles when the taskbar begins to fill up
3. Never combine – this option behaves like Windows XP and never combines programs into icons

Please let me know via the comments section below if something is not clear.