Access Input Mask Cheat Sheet

Use an Input Mask to control data entry in an Access database. For example, you can force users to enters  a phone number with an area code or an employee identification code with 2 letters followed by 3 numbers.

Below is a cheat sheet for Access input masks.

Input mask cheat sheet guide

(000)-000-0000 will force the user to enter a phone number with an area code.

(999)000-0000! will force to user to enter a 7 digit phone number but is optional for the area code. The exclamation mark causes the input mask read the numbers from left to right.

LL-000 will force the user to enter 2 letters followed by 3 numbers.

>L<??????????????? will make the first letter capitalized and the following letters lower case.

Check out my Access keyboard shortcuts guide or other Access blog posts by clicking on the Access link on the right sidebar.

Posted in Access | Tagged , | 2 Comments

How to use Countif and Countifs Functions in Excel for Baseball

This blog post I will share how to use the Countif and Countifs formulas.

Let me use some baseball stats of my favorite team, the Reds.

raw data

The easiest thing to do with the raw data is to create range names off the selection. After I select the data including the header row, I click the formulas tab–>create from selection. This steps creates range names in columns.

Create from selection Excel

If you are not sure about using range names in Excel, check out my blog post tutorial on why you want to use range names if you are not doing so.

Now we are ready for the countif function. I will use the countif function to count how many times the Reds won for the entire winner column. The countif looks at a column of data and adds a count for each condition you specify.

countif excel

The countif function looks at the Winner range and counts each time “Reds” appears within the range. Since “Reds” is text and not a number data type, I need to have quotes around the text string.

The countifs function is just like the countif function except that it can use multiple criterion. In this example I want to count how many times the Reds won while playing at home in Great American Ballpark.

countifs

My final result.

Excel final formulas

And yes, there is always “next year.”

Posted in Excel | Tagged | 2 Comments

How to Use Excel Traffic Lights with Conditional Formatting Dates

In this post, I’ll share how to set up traffic lights in Excel to show variances between a scheduled date and the actual date. This post in response to a comment Stephen wrote from an earlier post I wrote called How to format dates that are over 1 year old to turn red in Excel using conditional formatting.

Stephen’s comment:

Hi I am trying to create a spreadsheet using traffic lights in (2010) to show when a document review date has expired. One column will show the actual date of review, the next will shown the scheduled review date and the next column will contain the traffic lights,

green if carried out before or on the scheduled review date,
Amber if between 1 and 30 days overdue and
red if over 30 days overdue.

At the time of review the actual date column will then be updated to reflect the new date . The scheduled review column will also be updated to show next expected review date. Can you advise me best way to handle this please.

I would arrange the spreadsheet as seen below.

xcel formulas with dates actual minus scheduled variances

Then I would create a formula that subtracts the actual date from the scheduled date. For example =C2-B2 would be -36 days in D2 cell because the actual date happened 36 days before he scheduled date. If the scheduled date and actual date were the same the formula value would be zero. And if the actual date was after the scheduled date it would display in how many days.

Then select all the cells you want the conditional formatting rule of traffic lights on. Then click the Home tab–>Conditional Formatting–>New Rule.

Conditional Formatting New Rule

Make the following adjustments:

  1. Change the drop down in Format Style to Icon Sets.
  2. Select the Icon Style to be the traffic lights.
  3. Click Reverse Order
  4. Red traffic light is when value is greater than or equal to 30 number.
  5. Yellow traffic light is when less than 30 and greater than or equal to 1 number.
  6. Green traffic light is when less than 1 number.

Icon Sets Rules Manager Excel

At this point, you will see the traffic lights and the values of the formulas in column D. So the trick is to make the font change to white.

Change font to white trick

And the final result:

Traffic lights Excel Conditional Formatting

Download my example file to practice with at this link traffic lights conditional formatting.

Posted in Excel | Tagged | Leave a comment

Outlook Top 5 Tips and Tricks

Outlook is an awesome tool to stay productive. Here is a quick list of 5 tips you can start using to work faster.

  1. Drag a file into the email body while composing to automatically create and attachment. This works great if you have a document on the desktop that you want to quickly attach.
  2. Drag an email to the Calendar to quickly save an appointment. In Outlook 2007 and 2010, drag the email to the To-Do Bar. In Outlook 2013, drag the email to the Calendar icon from the navigation pane. Then save after editing the date/time. All of the notes that were in the email body will now be apart of the appointment details.
  3. Switch your Outlook signature with a right click. Outlook allows for multiple signatures. Access the signatures from the File tab–>Options–>Mail–>Signatures.
  4. Right click someone’s email and then choose “add to contacts”.
  5. Create re-usable text with Quick Parts. Select your text, then click on the Insert tab and choose Quick Parts–>Save Selection to Quick Part Gallery. When you want to reuse the text, simply click on Quick Parts and then choose your item you want. This is fast way to repurpose content.
Posted in Outlook | Tagged | 6 Comments

How to Format Dates In Excel That Are Over One Year Old To Turn Red, Yellow If Eleven Months Ago, And Green If Between One and Ten Months Ago

I’m writing this post in response to a comment that Charles wrote in my “How to change the color of an Excel date to red if over 1 year old”  post. It was such a good comment I thought I’d share my thinking with the rest of you in a new post.

I am tracking the training status of the people on my project. All training has to be taken annually. I want to set it up where the cell turns green if the date in the cell is between 1 and 333 days from today’s date i want to set it up where the cell turns yellow if the date in the cell is between 334 and 364 from today’s date I want to set it up where the cell turns red if the date in the cell is 365 days or more from today’s date.” – Charles

Let’s start with making the dates turn red if the training date has occurred over 1 year ago. In my screen shots below I’m using date cells that start with B2 so I select the first date all the way down to the last date. I click Home tab–>Conditional Formatting –>New Rule. Then I choose “Use a formula to determine which cells to format.”

I click format and select a red fill and then I write the formula.

=B2<=TODAY()-365

Over 1 year red

The TODAY() function is volatile and updates to the current date. Excel has a serial number value for each date. For example Jan 1, 1900 =1, Jan 2, 1900 =2, Jan 3, 1900 =3, Jan 4, 1900 =4 and Sep 19, 2014 =41,901. Everyday is another plus one. So we can use the greater than > and less than < operators to compare dates against the TODAY() function. You have to think in numbers and older dates will always be lesser than newer dates. So if you were born a long time ago you actually have a smaller Excel number than a someone from a younger generation.  And this is coming from one who just bought his first pair of reading glasses. :)

After I create the first conditional format with red cells, I  select the same cells and repeat the setup for the formula new rule for yellow colored cells. This will format cells yellow if the training date occurred 11 months ago.

=AND(B2>=TODAY()-364,B2<=TODAY()-334)

This uses the logical AND() function which looks at 2 conditions to check if the date is at the 11 month ago mark. The AND() function looks to see if all conditions are true. Specifically, it checks to see if the date is between 364 and 334 days ago. So it will turn yellow to help recognize a date that is close to reaching the 1 year mark.

1 month to expire training yellow
The final step is to select the date cells then click the create new rule for conditional formatting with a formula.

=AND(B2>=TODAY()-333,B2<=TODAY()-1)

 

Between 11 months and today green

The Rules Manager is used to make edits to the conditional formatting. So if your rules gets messed up you would need to click Conditional Formatting –> Rules Manager.

 

Excel Rules Manager

Note: When you are writing the formula you do not want to use your mouse to click on the first cell of dates because it would make B2 absolute referenced to $B$2. If that happens just delete the $ signs. You only need to have a relative reference and Excel will automatically apply the formula to all cells selected. The “apply to” section should have the absolute reference since that is the range that is not being copied anywhere. The format painter is also a nice choice is you want to copy the conditional formatted cells from one worksheet to another. Even works from one workbook to another workbook. So feel free to download my sample file spreadsheet that has all these rules in it.

If you like this post and be sure to sign up for the mailing list so you will receive an email for each of my new post. And be sure to check out my YouTube video on Conditional Formatting examples.


On a parting note, be sure to stop by my wife’s blog to see what’s for dinner tonight.

Posted in Excel | Tagged | 4 Comments

Use Excel’s Color Scales to Spot an Outlier

Can you spot the cell that is different than the rest in the screenshot below?

Before

excel before

 

Select the cells, click the Home tab then Conditional Formatting–> Color Scales. Your outlier will stick out with a different color than the rest.

After

excel color scales

 

Posted in Excel | Tagged | Leave a comment

Excel Conditional Formatting on Sales Greater Than $500,000 [Video]

Posted in Excel | Tagged | 1 Comment