Create a Countdown Timer With Dates in Excel

This post will share how to create a date countdown timer in Excel. The final result will result in putting together multiple formulas to show how many days, hours, minutes, and seconds there are leading up to a future event.

First I type some labels in A1 through A4

A1 = “Today’s Date and Time”

A2 = “Hour”

A3 = “Minute”

A4= “Second”

I enter formulas in column B.

B1 =NOW()

B2=HOUR(B1)

B3=MINUTE(B1)

B4=SECOND(B1)

now function

The NOW function returns the current date and time. Pressing the F9 button key will update the value.

The HOUR function returns the current hour expressed in military time. Zero is 12:00AM and 23 is 11:PM.

Hour function

The MINUTE and SECOND formulas return a value between 0 and 59.

I create a custom format on the NOW function result in B1 to have AM/PM displayed.

am pm excel custom format

Then I enter some labels in D1 through D4 and values in cells E1 through E4

Event Date – 12/20/2016 8:00 PM

Event Hour – 20

Event Minute- 59.9999

Event Second – 59.9999

More labels in cells G1 through G4 and formulas in cells H1 through H4.

Days =INT(E1-B1)

Hour =IF(E2>=B2,E2-B2,24-B2+E2)

Minute =INT(E3-B3)

Second =INT(E4-B4)

event date

I  create an IF function that will determine if the event hour is greater than or equal to the current hour. If true, then event hour – current hour. If false, then =24-current hour+event hour. Times are in military 0 through 23.

if statement hours measured

The INT function rounds down the number to the nearest integer.

I create range names in my individual values from H1 through H4.

range names

Now I can see how many days, hours, minutes, and seconds are left from now till the event date on 12/20/2016 at 8:0o PM. This is all with subtraction formulas in  cells H1 through H4.

I finish up with creating a formula that concatenates the values into a string text output.

The & Ampersand is used to join text strings. I use the Range names in the formula in between the labels.

Final result:

756 days 23 hours 31 minutes 33 seconds left

Download my example spreadsheet  Date Countdown Timer.

 

Posted in Excel | Tagged | Leave a comment

How to use an Input Box in Excel

In this post, I’ll share how to collect data in an Excel spreadsheet using an Input Box. I’ll try to make it as easy as possible with lots of screenshots. The purpose of my example is to have someone click a button in Excel that collects data. That “input box”  will lead to a box that asks them to enter units and then a price value. After inputting those values, Excel will perform VBA code that will automatically enter the number of units in B1, the price value in currency in B2 and a formula result in B3.

Let’s get started. Using Excel’s Developer tab, I click the Insert menu then Command Button(Active X Control).

insert command button active x control developer tab excel

Then I left click and drag the shape and location of this command button.

command button

I then right click the command button and click View Code. This takes me to the Visual Basic editor in which I can create the VBA code to make all this happen. Switching between Excel and the code window can use the keyboard shortcut AlT+F11.

Active X command button view code right click

With the insertion point between the Private Sub CommandButton1() and End Sub lines I create variable declarations by typing:
Dim Units as Integer
Dim Price as Currency
Dim Total as Currency

Dim stands for dimension. It is used to create space or dimensions of allocated memory. It helps set up declaring a variable.
Dim declarations of varables

Now I’m going to set up an expression that will input the value of the Units.
I type
Units = InputBox(“How many units did you sell?”)
Range(“B1″).Value = Units

The user will get a prompt to enter units and then the variable I declared (Dim) is going to store that value in memory and enter it in cell B1.

Units command Button screenshot

Whatever the user enters in the InputBox will immediately go in cell B1. Also have to enter a value or else a null value will cause the code to debug.
Units input box Excel

Now I type the Price input prompt I want from the user.
Price = InputBox(“What Price?”)
Range(“B2″).Value = Price

Price = INputBox Excel what price

And this is what happens in Excel after for this portion of the code. User enter a price and the price is then entered automatically in cell B2.

Price Input Button Excel screenshot

Price Input Button Excel

The last line of code I type is a formula result.

Range(“B3″) = Units * Price
Command Button Total

I then create a caption for the Command Button by clicking on the properties button (from the Developer Tab) and type a caption I want the button to have. I need to make sure I’m in Design Mode to do this or else it will just want to run the macro.
Command Button Caption

Download my Excel keyboard shortcuts here. And if you like this post, make sure you sign up to receive others by joining my mailing list.

Posted in Excel | Tagged | Leave a comment

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 | 9 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 | 10 Comments