Home Archives for Excel Date and Time Functions
December 9, 2010 2 Comments

How to Work with Dates in Excel

The majority of the questions that I receive from viewers center on how to use and format DATES & TIMES in Excel. So, I have decided to create a series of lessons to show you how to work with dates and times in Excel.  This is part one in a continuing series of free Excel Video Training Lessons.

50 Best Tips for Excel 2007

Excel Tips

Learn How to Work with Dates

  • Use a Keyboard Shortcut to enter the current date in a cell – to “time stamp” the date.
  • Use the TODAY() Function to enter a “dynamic” date function in a cell – so that your date always reflects the current date.
  • Distinguish between how Excel “stores” a date – as a serial number – and how Excel formats a date – how you choose to format a date.
  • Tell if a date was entered correctly – as a number – or incorrectly – as “text” by seeing how it is aligned in the cell (right or left).
  • See why a fraction could be recognized as a date – and how to correct this “got’cha” step!
  • How to create a series of dates in your spreadsheet.

In my experience, the majority of “Excel problems” are based around incorrect date and time entries. So, whether you are new to Excel or a veteran who gets frustrated with Time and Date calculations, this lesson is for you.

If nothing else, you will pick up some great Keyboard Shortcuts in this lesson!

Follow this link to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Work with Dates in Excel [ 8:55 ] Play Now | Play in Popup | Download (796)
Link to this post!
Filed Under: Excel Basics, Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
November 23, 2010 4 Comments

How to Use the Undocumented DATEDIF Function in Excel

Want to get in on a secret? The DATEDIF() Function is a well-kept secret in Excel. You will not find it in the list of Excel Functions. And, you will not find any documented Help for it from Microsoft! Amazing, because it is a great function to use. Be sure to bookmark this video, because this may be the only help that you get for the DATEDIF Function!

DATEDIF() has three required arguments: Start_Date, End_Date, Date Code.

Six Date Codes

  1. “Y” – Gives you the difference in “years” between the starting and ending dates.
  2. “M” – Gives you the difference in “months” between the starting and ending dates.
  3. “D” – Gives you the difference in “days” between the starting and ending dates.
  4. “YM” – Ignores the years and days to give you the difference in “months” between the starting and ending Months.
  5. “YD” – Ignores the years to give you the difference in “days” between the starting and ending Days and Months.
  6. “MD” – Ignores both the years and the months to give you the difference in “days” between the “day” of the Start_date and the “day” of the End_date.

You can add “clarifications” to the results by using the “&” (Ampersand) and a “text label.”

I use DATEDIF frequently – for example to find out a person’s exact age or to document the number of years an employee has been worked for you.

Follow this link to visit my Archive of  Video Lessons for Time and Date Functions in Excel

Click here to watch this Excel Video Lesson in High Definition, Full Screen Mode on YouTube.

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
How to Use the Undocumented DATEDIF Function in Excel [ 7:43 ] Play Now | Play in Popup | Download (839)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
September 5, 2010 1 Comment

Quickly Create a Series of Dates in Excel

In this lesson, I demonstrate two approaches to creating a series of dates in Excel:

  1. Use Excel’s AutoFill Options to drag your mouse to fill in the series. Be sure to use the “AutoFill Options” button when you release your mouse.
  2. Use an Excel Formula to increment the series of dates (by day, week, month, year, or even weekday.) This approach gives you the most flexibility if you will use a different starting date on copies of your worksheet.

I devote several lessons to Excel date formulas and date formatting on my DVD, “The 50 Best Tips for Excel 2007.” Click here to add it to your shopping cart.

Lear how you can “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Quickly Create a Series of Dates in Excel [ 5:41 ] Play Now | Play in Popup | Download (2096)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, MS Excel Tips, Podcast Tips & Timesavers, Video Training Courses Tagged: , , , , ,
May 15, 2010 Leave a Comment

I Have Updated My Index of Excel Video Lessons

As part of the process of reorganizing my website, I have updated my “Index of Excel Video Lessons” page. The content of this page will be updated as I add each new Excel Video Training Lesson. So, I suggest that you click here to go to the Video Index and then save the page as one of your “Favorites” if you want to learn how to really get the most out of Excel or to find a quick answer when you are using Excel.

On the Index Page you can see an organized listing of my Excel Videos by Category – e.g. Formulas, Formatting, Pivot Tables “What-if” Analysis, etc. Each lesson is “hyperlinked” so that you can just click on the topic to go directly to the lesson that you are interested in.

I indicate in the title if the lesson is specific to Excel 2007.  All my new Excel video lessons are created using Excel 2007.

If there is a topic that you would like me to cover, send me an e-mail message – danny@thecompanyrocks.com

If you enjoy my video instruction, I encourage you to purchase my DVD, “The 50 Best Tips for Excel 2007.” The DVD contains over 5 1/2 hours of Tips, Tricks & Techniques to help you to master the program – and to save a considerable amount of time while working in Excel! Click here to enter my secure shopping site.

Thank you for your support and encouragement!

Danny Rocks

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Everyday Calculations in Excel, Excel Basics, Excel Data Tables and Lists, Excel Date and Time Functions, Excel Financial Tools, Excel Formulas, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, MS Excel 2007 DVD, MS Excel Tips, MS Excel Training, Online Store for The Company Rocks, Pivot Tables, Text Functions, Tips and Time Savers, Training, Video Training Courses, What-if Analysis Tagged: , , ,
October 30, 2009 Leave a Comment

Calculate the Days Worked on a Project

Do you manage Projects? Do you create Project work schedules? Do you need to know how many days were actually worked on your Project?

If so, this Excel video lesson is for you. A viewer asked for my help. He did not want to use MS Project for his work schedules. Rather, he wants to know which Excel Functions to use for his Project work schedules.

Excel Functions Covered in this Tutorial

In this lesson, I demonstrate how to use two functions included in the Excel “Analysis Tool-Pak”:

  • =WORKDAY()
  • =NETWORKDAYS()

Index of Excel Video Tutorials

Search for a specific Excel Video Lesson on this website in my Index.

Watch Excel Tutorial on YouTube

Click on this link to watch this Excel Tutorial on my YouTube Channel – DannyRocksExcels

Secure Online Shopping for My Resources

Want to learn how to get the most out of Excel 2007?

My DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007” is available for purchase.

 

 

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Financial Tools, Excel Functions, Excel Training, Video Response to Viewers Tagged: , , , , , , ,
April 3, 2009 Leave a Comment

Three Frustrations When Calculating TIme in Excel

When I am training a class to use MS Excel, I always get at least one question about performing time calculations. I sense that the person asking the question is frustrated. When I ask, “How many of you are frustrated when you try to perform a calculation to total time periods in Excel?” nearly every hand is raised! And they are right; Excel’s time calculations can cause frustration.

In this video lesson, I will show you how to overcome three common frustrations that you may have when performing time calculations in Excel.

Here are the steps to follow in this Excel Training Video:

  1. Format the cell that contains the formula =End_Time – Start_Time) to the Custom Number Format h:mm
  2. I encourage you to use this formula instead of a simple formula: =IF(End_Time < Start_Time, End_Time + 1, End_Time)-Start_Time.
  3. This will eliminate any possible “Negative times” which result in a cell filled with ######### A Negative Time is frequently the result of “Time Periods that Span Midnight. e.g. Start @ 11:00 PM and End @ 3:00 AM
  4. TIP: Use this Formula =MOD(End_Time – Start_Time, 1) This is the MODULUS Function.
  5. When you calculate the total number of hours and the result exceeds 24 hours, use this Format [h]:mm

Search My Index of Excel Video Lessons

 “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Functions, Excel Training Tagged: , , , , , ,
October 28, 2008 3 Comments

Viewer Request: How do I calculate dates in the future?

Today’s lesson is my answer to a viewer’s question: “How does Excel calculate a date in the future?”

The viewer wrote me because they were having trouble writing a formula that would return a date “N-Months” in the future. Or “3 years in the future” from a specific starting date.

I am asked this question frequently. In this short Excel video lesson I demonstrate how you write the formula.

Here are the steps to follow in this Excel Training Video:

  1. Use the AutoFill feature and Options to increment your “Starting Date.” I recommend using your “right-mouse button” to AutoFill because it automatically brings up a Menu of Options when you release the Right-Mouse Button.
  2. In response to the viewer’s question, we will choose “Fill Months.”
  3. Remember that we are using a “Hard-Coded” date as our “Starting Date,” so each value is really a “constant value.” This may not be the optimal result that we are seeking.
  4. In order to provide flexibility (with your starting date) you need to learn how to construct a formula that will increment (in this case the MONTH) our values.
  5. Use the “DATE() Function.
  6. For each of the “Arguments,” use the YEAR(), MONTH() and DAY() functions.
  7. To answer my viewer’s question we use MONTH(A2) +1 in the “Month Argument.”
  8. To “Increment the Year,” we use YEAR(A2) +1 in the “Year Argument.”
  9. Finally, write a “Formula” to verify that Excel is properly accounting for “Leap Years.”

Search My Index to all Excel Video Training Topics

The 50 Best Tips for Excel 2007″ DVD is now available for purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training Tagged: , , , , ,
October 25, 2008 Leave a Comment

Use the =SUMIF() Function to Tally YTD Sales

Many of my clients keep a “running total” of sales. They put “Dates” in Column “A” and enter each day’s sales in Column “B.” Next is Column “C” which contains the formula =SUM(B$:B250). That is fine – it works.

However, if you use the =SUMIF() function to total Year-to-Date (YTD) Sales you gain flexibility, save space and save time – because you do not have to scroll down several screens to see your YTD Sales total!

In this video, I show you how to use “Named Ranges” in a =SUMIF() Formula to keep track of your cumulative sales total.

Here are the steps to follow in this lesson:

  1. Traditionally, you use a formula like =SUM(B$3:B250) to give you a “running total” for YTD Sales.
  2. The=SUMIF() Function returns the SUM of vales that meet a “Single Criterion” (the IF portion) .
  3. The 1st Argument is – What is the Range that you want your criteria to evaluate? Consider “Naming” this Range – it makes it easier to write your formula and explain your formula.
  4. The 2nd Argument is – What is your criteria? Be sure to use this syntax – “Comparison Operator”
  5. In the video example we also need to include the “&” to join the cell “D2.” Cell “D2,” in this example, contains the Function =TODAY()
  6. The 3rd Argument is – What Range do you want to SUM to return the values that meet your Criteria?
  7. The =DATE() Function returns the serial number for a specific date. It requires 3 Arguments. =Date, Year, Month, Day)
  8. You will save time and gain productivity when you “Name Cells and Ranges” and then you them in your formulas.
  9. Use the F3 shortcut key to “Paste Names” into your formulas.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Find the Excel Video Lesson that you want – Index to all Excel Topics

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Functions, Excel Training, SUMIF and SUMIFS Functions Tagged: , , , , ,
September 30, 2008 Leave a Comment

How to Solve Four Frustrations When Calculating Time in Excel

Have you ever been frustrated when you:

  1. Entered a “Time Value” in a cell only to find that Excel did not recognize it as a “time value?”
  2. Wrote a formula to calculate the elapsed time between “start and finish” only to find that Excel returned a “date” (e.g. 1/15/2008) and not a number?
  3. Wrote a formula to calculate the hours worked for your “Graveyard Shift” (those whose work hours cross Midnight) only to get a cell full of ########?
  4. Used the AutoSum function to calculate the total hours worked in a week only have Excel return a “silly” total like 1.7915?

Well, join the club! Most of my clients have experienced these four frustrations when they try to calculate “time values” in Excel. In this short video lesson, I will demonstrate how to solve each of these four frustrations.

I’d like to receive your feedback on this video lesson. Have you ever encountered this frustation? Did my explanation help you? What additional topics would you like me to cover?

You can email me – danny@thecompanyrocks.com

Here are the steps to follow in this video lesson:

  • Excel stores Time values as decimal numbers. Midnight is 0.00; 6:00 AM is 0.25; Noon is 0.5
  • Be careful when you enter “time values” into a cell. 12:00PM is considered “text.” You need to include a SPACE before the PM e.g. 12:00 PM in order to tell Excel that this is a “time value.”
  • Use this Keyboard Shortcut Ctrl+Shift+~ (Tilde) to see how Excel stores a “time value” in a cell.
  • When calculating “elapsed time”, use a “Custom Format” for  the cell with the Formula E.g. [h]:mm
  • When you calculate “elapsed time” for cells that “cross midnight” you will get ###### as your result unless you use a formula like =MOD((End_Time-Start_Time), 1)
  • When you total “time values” that will exceed 24 hours (E.g. Hours worked in a week) format the cell containing the formula with a “Custom Format” e.g.[h]:mm

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Training Tagged: , , , , ,
« Previous Page