Скачать или смотреть Convert Text to Time Values with SUMPRODUCT (Data Cleansing Part 2)

Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint...

This is the second video in a series of solutions for our Data Cleansing Challenge. In this video I explain how to use a formula with SUMPRODUCT to convert the time stored as text into numeric time values in Excel.
Download the Excel file to follow along:

Read the full article: https://www.excelcampus.com/functions...

In a previous video (   • Excel Data Cleansing Challenge: How To Con...  ) I shared this challenge to convert time/duration stored as text into time values that can be used for calculations and analytics.

Thanks to everyone that commented on the video and blog post with solutions. In this video we look at a solution that combines the text functions from part 1 (   • Convert Text to Time Values with Text Func...  ) with the SUMPRODUCT function.

This allows us to convert all the time increments (hours, minutes, seconds) into seconds within a single formula. We then divide by the total number of seconds in a day to return the time value.

In the video I walk through writing the formula.

You will learn how to use an array (list) of values within SUMPRODUCT. We can feed the search function multiple values to extract time period.

The array of values are listed in curly brackets.

{"h","m","sec"}

I also explain how the SUMPRODUCT function calculates these arrays by multiplying the items together and then summing them up.

In the video I use an Excel Table. Checkout my video on a Beginner's Guide to Excel Tables (   • Excel Tables Tutorial: Beginners Guide for...  ) if you are not familiar with this awesome feature of Excel.

Here is a link to the first solution video:    • Convert Text to Time Values with Text Func...  

In the next video we look at how to use Power Query to solve this challenge (   • Convert Text to Time Values with Power Que...  ).
00:00 Introduction
01:55 SUMPRODUCT Function
09:39 SUMPRODUCT Explanation
11:10 Convert Text to Time