Thursday, September 17, 2015

Quick Excel Help: Date and Time Formats

I received an email from one of my readers asking a question about date and time formats in Excel and I figured I would share my answer here in case anyone else has the same question.

Question: Hi Nick, I am having trouble with Excel time date formats and was hoping you can help. I have a data field with a date time format and I want to separate this out with date in one column, time in another, & hour of the day  in another. Can you please tell me how to do this?

Answer: There are a number of date and time functions in Excel that should be helpful to you. I’m not sure if this is exactly what you’re looking for but try these formulas out:

In column a: =now()
Column b: =month(a2)&”/”&DAY(A2)&”/”&YEAR(A2)
Column c: =HOUR(A2)
Column d: =minute(A2)
Column e: =Second(A2)
Column F: =TIME(C2,D2,E2)

excel date and time formulas

Do you have any other suggestions for this reader’s question?

Tuesday, September 8, 2015

Gantt Chart Template Excel 2010

A Gantt chart template is included in my Master Project Management Template file that I give away as a free gift for joining my email list. I have a new and improved version that is a bit more user friendly and simply better to look at.
gantt chart template excel 2010

A Gantt chart is a bar chart that shows the tasks of a project, when each must take place, and how long each will take. As the project progresses, bars are shaded to show which tasks have been completed. People assigned to each task can also be represented. Gantt Charts are also called a Milestone Chart, Project Bar Chart, or Activity Chart.

How to Use the Gantt Chart Template

Follow the steps listed below to use the template spreadsheet:

1. Enter up to 30 tasks in the cells provided.
2. Enter the Start Date for each task and the number of days to complete it.
3. Enter the Percent Complete for each task. This is required, even if 0%.

Watch the video below to see the Gantt Chart Template Excel 2010 in action.

When to Use Gantt Chart

The list below shows the ideal time to use a Gantt chart:
  • When scheduling and monitoring tasks within a project.
  • When communicating plans or status of a project.
  • When the steps of the project or process, their sequence and their duration are known.
  • When it’s not necessary to show which tasks depend on completion of previous tasks.
This chart is very easy to use and can be quite helpful when managing a large project. Download the template by clicking the link below:

How often do you use Gantt Charts? I’d love hear about your experience so please let me know in the comments below.

Wednesday, August 19, 2015

NCAA 2015 College Football Helmet Schedule

It's hard to believe but the 2015 college football season is about to begin in less than two weeks! My Buckeyes are the defending National Champions! I never would have expected that. In fact, I said this after their second game of last season:
Oops. I’m glad I was so wrong. And that’s why I love watching football – it’s unpredictable!

This 2015 college football helmet schedule in Excel includes every team from all ten conferences plus independents. Every game is listed as either home, away, or neutral site (noted at the bottom of each sheet).  A college football helmet schedule spreadsheet may be available on other websites but, to my knowledge, this is the only downloadable Excel version and unlike some of the others is 100% FREE! Download it today using the link below:

Monday, August 3, 2015

When to use macros in Excel?

Automating tasks and process in Excel with macros can be a great way to save time and improve efficiency. But not every task requires a lengthy macro code to be written, tested, debugged, and rolled out to the entire team. There is a good time and a bad time to use macros in your everyday job. You don’t want to waste time writing a program if it is never going to be used, or if there is a simple non-macro solution. Sometimes using macros can be a little overkill, you know, like fishing with dynamite.

How do you know when you should write a macro to solve a problem and when you shouldn’t? Listed below are the cases when it is a GOOD time to implement an Excel macro solution.

  • The most obvious situation to use Excel macros is to replace manual, repetitive tasks. If you find yourself doing something over and over, like copy-paste-copy-paste, you should definitely be automating that task. 
  • If you ever think to yourself “there has to be a better way to do this” then that is a good clue that VBA macros may be the way to go. 
  • Along the same line of thought is reducing the time to manage large numbers of spreadsheets. You can do batch processing with Excel macros, like converting hundreds of Excel files into PDFs, exporting data to Word or PPT, taking screenshots, or combining Excel files.
  •  Macros are useful when there are large numbers of workbook users who need to reuse variants of the same spreadsheets repetitively. Instead of using a template, sometimes it makes more sense to have a macro that builds the foundations of a new workbook on the fly. 
  •  A macro is a good solution if there are tasks that would be practically impossible to do manually. 
  • You should use a macro if there are some tasks which you want to be very sure the user will not miss, such as a series of steps that must be executed in a certain order. Macros can be a form of quality control and are a great way to eliminate human errors. 
  • Automated solutions can enhance the productivity of non-skilled Excel users. If the projects are just taking too long, maybe due to lack of proper training, you can eliminate some of that headache by using macros. 

As you can see, there are countless tasks and processes than can be made more efficient by using macros within your Excel spreadsheets. You can find some of the macros I've written on the spreadsheet downloads page.

Tuesday, July 28, 2015

2015 NFL Helmet Schedule Spreadsheet

The 2015 NFL season is just around the corner! The inaugural Hall of Fame game will take place in Canton, Ohio on Sunday, August 9th. Four weeks of preseason games will follow before the start of the regular season on Thursday, September 10th.  For the complete 2015 NFL schedule, download my free spreadsheet that includes all NFL helmets from every team. It’s a fun way to look at your favorite team’s opponents.

Even though basing a team's supposed “ease of schedule” on the previous year's record is a faulty premise, it's still fun to do and happens all the time. Once again, I’m not holding out much hope for my Cleveland Browns. They still haven’t found their franchise quarterback. I think the NFL season is even harder to predict than college football. Teams at the bottom one year can go right to the top the next. That’s why we love it!

2015 nfl helmet schedule excel

As for the actual spreadsheet itself, it’s very simple at this point, and proves not every Excel file has to have macros or conditional formatting. There are no major formulas or tricks, just images of each of the football helmets. In the future, I am planning on linking the helmets to each team which will make updating the schedule for next year much easier. And I’m always open to suggestions for improvement.

Download the football helmet schedule using the link below.

Yes, it takes quite a long time to assemble all the NFL helmet logos each and every season but it’s a fun way to look at the schedule and all NFL helmets at the same time. Luckily, the NFL doesn't see as much change year to year as college football does with their constantly changing conferences.

In the comments below let me know what you think about your favorite team’s chances this year!

Wednesday, May 27, 2015

How to send emails with custom subject lines in Excel

If you type an email address into Excel and press enter, it automatically becomes a hyperlink. If you click the link, it will create a new email in your default email client with the To: line already filled in. The subject line, however, will be empty.

excel email hyperlink

So how do you automatically fill in the email subject line from Excel? One way to populate the subject line is by using the HYPERLINK formula. Follow this example:

In column A, place all of your email contacts. In column B, place all of your subject lines. Your email subject lines can all be the same or they can each be unique. In column C, we’ll place the following HYPERLINK formula:


Now, when you click the hyperlink created by this formula it will create a new email addressed to the recipient you listed in column A, and will feature the subject line you listed in column B. If you want to add email hyperlinks with constant subjects this is the way to go.

To CC someone, add this to your formula: “&cc=”, A2 where A2 is the cell location of the recipient’s email address.

To take it a step further, we can also add text in the body of the email by expanding our Excel formula. In column C, place the text you want to appear in email’s body and add the hyperlink formula to column D:


We can take it even further by including the email recipient’s name and adding our signature. Make it look nice by adding some formatting. To add a line break to your email’s body text, use: %0D%0A. You can add this to the formula or place it in a reference cell. So, to compose a complete email in Excel, including recipient, subject line, and body text with line breaks, use the following formula:

This really is the ultimate formula for sending emails from Excel.

To add an attachment it seems like you should simply be able to add “&attachments=”,C2 but in Outlook2013 I get the error message “Cannot start Microsoft Outlook. The command line argument is not valid. Verify the switch you are using.” Anyone know how to fix this issue? In the meantime, the best bet to add an attachment may be to use a VBA macro, as demonstrated here.

 It is possible to manually manage an email list from Excel. But with all the automated tools out there today, why would you? If you have a good reason to I’d love to hear about it. Maybe it’s because someone does not want to pay for an email subscription/list building service? An email list building and sending tool like Aweber is only around $30/month (and what I use to send out my free email tips). In conclusion, you can manually manage email lists with Excel, but automated services like Aweber make it so much easier.

Monday, April 6, 2015

How to input military time into Excel

I have to admit, the title of this post is a little bit misleading because there is no direct way to input military time into Excel. If you try to input "1300" as 1:00PM, Excel has no way to know that you're entering a time as opposed to a number. In order for Excel to recognize your input as a time and not 1,300 you must enter a colon and enter as "13:00". Basically, formatting the cells for dates and times only affects how the contents of the cell is displayed and not how the information is entered. So if you enter the military time with a colon and format the cell as Time then 1300 will be converted to 1:00PM.

Here's why: Excel stores dates and times as days and fractions of a day, where the number 1 equals January 1st, 1990. Entering 1300 into a cell leads Excel to interpret that as the 1300th day since January 1st, 1900 at 12 midnight.  Enter 1300 into a cell and format it as Time. Notice the value displays this: 7/23/1903  12:00:00 AM. Interesting, no?

how to convert military time in excel

Going back to the original question, how to input military time into Excel, my suggestion is this: entering a colon while inputting the military time is extra work. We always want to make data entry fast and painless as possible. So let's use a formula that will enable us to enter military time as 1300 but then display the regular time.  If we input the value of 1300 in cell A1, enter the following formula into cell B1: 


In the picture above, you can see the how the military time is entered and the formula converts it to standard time. This post was inspired by a question from a reader on our Excel Spreadsheets Help Facebook page.