Learn Excel from Bill Jelen with this daily 2 minute video podcast.

Tuesday, April 13, 2010

Pivot Date Grouping - Episode 1201

Deb from Wisconsin noticed that her date grouping was wiped out after refreshing a pivot table. Episode 1201 takes a look at how to find the bad dates and solve the problem.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , , , , , ,

Friday, November 27, 2009

Weekly Report - 1153 - Learn Excel from MrExcel Video Podcast

Lotfy from Egypt asks how to create a weekly report from daily transactional data. Mike and Bill show you several different methods in Episode 1153.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Thursday, October 29, 2009

ADM Calculation - 1133 - Learn Excel from MrExcel Podcast

Patricia called the other day with a problem which will be familiar to everyone who works in a public school district. The state pays the school for every student who is enrolled on the magic date of October 1. Given a start date and end date, how can you tell if the date is in that range? Episode 1133 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, October 28, 2009

Filling Quarters - 1132 - Learn Excel from MrExcel Podcast

The Excel fill handle is really cool with quarters. But wait....it can not do quarters and years. But wait...it can do them but in a hideous format. Episode 1132 asks - "Does anyone have a better way?"


This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, October 23, 2009

First Week of Month - 1129 - Learn Excel from MrExcel Podcast

A dueling podcast...how to find the first week of the month. Mike and Bill show you various ways in Episode 1129.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Monday, October 05, 2009

Opening Balance VLOOKUP - 1115 - Learn Excel from MrExcel Podcast

Brett from Australia asks how to format a date to include the words Opening Balance so that he can do a VLOOKUP into a report. The format is easy, the VLOOKUP is tougher. Episode 1115 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Thursday, October 01, 2009

Years Months Days - 1113 - Learn Excel from MrExcel Podcast

Med asks how to calculate Years, Months, Days between two dates. Episode 1113 discusses the elusive DATEDIF function.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, September 22, 2009

Timestamp - 1106 - Learn Excel from MrExcel Podcast

David from Australia sends in a cool trick for adding a timestamp to a spreadsheet. Episode 1106 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Thursday, September 17, 2009

Blank Row for Missing Dates - 1103 - Learn Excel from MrExcel

Sam from Vienna sends in today's question. In a database of events, how can he add new blank rows to represent every missing day? Episode 1103 shows you one method.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, September 04, 2009

Dueling: Random Date - 1095 - Learn Excel from MrExcel Podcast

Mike and Bill offer different ways of generating a random date between two dates. Episode 1095 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Thursday, August 06, 2009

Quarters or Weeks - 1074 - Learn Excel from MrExcel Podcast

LearnAccessByCrystal sends in a cool tip that will allow you to format dates to show quarters, weeks, and more. Episode 1074 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, June 10, 2009

Fiscal Quarter UDF - 1033 - Learn Excel from MrExcel Podcast

If your fiscal year does not end on December 31, then all of the cool date grouping functions available in pivot tables will not work for you. In Episode 1033, you will see how to add a couple of user-defined functions to your personal macro workbook to simplify the conversion of date to Fiscal Quarter or Fiscal Year.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, May 21, 2009

Coercing Dates - 1019 - Learn Excel from MrExcel Podcast

You can solve the MWF problem from episode 1018 using an incredible array formula from the book Excel Gurus Gone Wild. Episode 1019 takes a look at how to coerce an array of dates from a start date and end date cell.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, May 12, 2009

Fiscal Quarters - 1012 - Learn Excel from MrExcel Podcast

Vaibhav asks how to group a pivot table up to fiscal quarters. While this is easy if your fiscal years ends on December 31, it is not so easy for other year-ending dates. Episode 1012 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, April 06, 2009

MrExcel's Learn Excel #986 - Rounding NOW()

Many people use =NOW(), but it is frustrating when you try to calculate how many days away a certain event is occurring. Episode 986 shows a method for solving this problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Thursday, March 05, 2009

MrExcel's Learn Excel #964 - Thu Mar 5, 2009

I bet you thought that I screwed up and put the date of the podcast as the title! But no... that really is the title. Tony asks how to change the long date format to show Thu Mar 5, 2009. Episode 964 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, February 10, 2009

MrExcel's Learn Excel #947 - Wrong Date Format

You get a CSV file from an International branch of your company and the dates are in M/D/Y when you use D/M/Y or vice versa. There is an easy solution. Episode 947 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Tuesday, May 06, 2008

Episode 752 - Truncating to Date

In today's podcast, we take a look at breaking Romas' date/time column into a date column using the INT function. Episode 752 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Monday, May 05, 2008

Episode 751 - Grouping Dates

Romas has data with both date and time in column A...but needs to summarize the data by date. In Episode 751, we take a look at using a pivot table to solve this problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, April 08, 2008

Episode 732 - Dates from Text to Columns

Jonathan notices a problem when he uses the Text to Columns wizard. Baseball scores such as 4-3 are converted to dates. In Episode 732, we'll take a look at how to keep those scores from being converted.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, March 07, 2008

Episode 710 - Pivot Filters

Learn how to filter a pivot table to only items from this week, last quarter, or next month. Episode 710 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Thursday, March 06, 2008

Episode 709 - Pivot Dates

Excel offers amazing tools to group daily dates to months, quarters, years, or weeks. Episode 709 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Wednesday, February 06, 2008

Episode 688 - Month End

Calculate the last day of this month, using a new trick sent in by Bob. Episode 688 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, September 13, 2007

Episode 592 - Missing Pivot Dates

Today’s question is a follow-up to yesterday’s question. How can you make sure that a pivot table shows you one row per day, even if there were no sales on a given day? Episode 592 shows two annoying solutions. If you have a better solution, please send in your ideas to bill @ MrExcel.com.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Wednesday, September 12, 2007

Episode 591 - Missing Dates

When you create a chart from data that might have missing dates, you might want Excel to plot the missing dates along the horizontal axis. In Episode 591, we take a look at why Excel sometimes chooses to use a time scale and sometimes does not.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Friday, July 27, 2007

Episode 559 - Zeroth Day

Erik points out that the best way to find the last day of this month is to ask for the zeroth day of next month. Episode 559 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Tuesday, July 17, 2007

Episode 551 - Red-Flag Dates

Van writes in with a suggestion about podcast 539. In that podcast, we were trying to calculate which customers had their birthday coming up soon. Van suggests using conditional formatting and the TODAY() function to flag the customers to whom you need to send birthday cards. Episode 551 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

Tuesday, July 03, 2007

Episode 542 - Shortcuts

Jonathan writes in to mention Ctrl+; enters today’s date. In Episode 542, I run through several of my favorite shortcut keys, including Show Formulas, Visible Cells Only, and more.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

Thursday, June 07, 2007

Episode 524 - Final Saturday of Month

Ammar from Iran sends in a question about how to find the last Saturday of any given month. Episode 524 shows how to use a couple of analysis toolpak functions to solve this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Monday, May 14, 2007

Episode 507 - JanFebMar Conversion

Your crazy software exported a file where the date column has the not-so-useful values like “Jan”, “Feb”, and “Dec” in a column. Episode 507 looks at a function to convert those values to real dates.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Wednesday, April 25, 2007

Episode 494 - Time Stripping

Jonathan from California has a column with date & time information, but would like to strip out just the times to do an analysis by hour. Episode 494 shows you two formulas that can be used to analyze the data by hour.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

Wednesday, April 18, 2007

Episode 489 - Non-Sorting Dates

Viewer George sends in a column of dates that refuses to be sorted. George says that he already tried converting the text dates to dates using the text to columns trick. In Episode 489, we’ll take a look at two methods to tell if your dates are really dates and how to convert them to real dates.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Thursday, April 05, 2007

Episode 480 - Text Dates

After importing data, your numbers and dates might be stored as text. While newer versions of Excel allow you to solve the numbers stored as text easily, the exclamation point dropdown never appears for the dates stored as text. Episode 480 takes a look at several methods for solving the problem.

This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn an Excel tip!

Labels: ,

Monday, February 19, 2007

Episode 447 - Dates as Numbers

Here is a bizarre problem sent in by a viewer. Many of you will think that you know the answer, but this problem is not as simple as it appears. In a worksheet, any dates are instantly converted to their 5 digit serial number. The actual date is shown in the formula bar, but the worksheet cells show the underlying serial number. Yes – my first thought was that someone had formatted the worksheet using a Number format, but this is not the problem. Hint: this is a simple spreadsheet set up to log information, and there is not a single formula anywhere in the worksheet. Episode 447 solves the mystery.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Thursday, February 08, 2007

Episode 440 - Virtual AutoFilters

Excel 2007 does offer a number of improvements in the AutoFilter arena. Episode 440 shows the new virtual date filters, such as selecting records from this week, next month, or last quarter.

This blog is the video netcast companion to the new book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!

Labels: , ,

Friday, January 26, 2007

Episode 431- Fill Weekdays

If you work Monday through Friday, Microsoft has a great secret shortcut for filling all of the weekdays in your spreadsheet. Episode 431 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Wednesday, January 03, 2007

Episode 414 - Joining Dates

If you try to use the concatenation character to join text with a date, you will not get the results that you expected. Episode 414 shows you how to modify the formula to properly format the date.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

Friday, December 08, 2006

Episode 399 - Workday Calculations

There are a couple of obscure functions in the analysis toolpak that will allow you to calculate how many workdays have elapsed. Enter a hire date and today's date, and you can calculate the number of workdays. This function even allows for you to enter a range of company holidays which are factored into the calculation. Episode 399 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Monday, October 09, 2006

Episode 357 - Date Differences

You have two dates in Excel and want to find the elapsed years and months. The solution is to use an ancient function from the days of Lotus 1-2-3. Episode 357 shows you how to use the function, as well as a cool formatting trick to show years and months in the same cell.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,