Look Who it Was Random video

Excel - Master Excel Manipulation: Coerce All Dates Between Two Given Dates - Episode 1019

686 views · Published 21 May 2009 · 5:12 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Master Excel Manipulation: Coerce All Dates Between Two Given Dates.

Welcome back to the MrExcel netcast, where we tackle all things Excel. In this video, we're going to dive into a problem that many of us face - analyzing a massive amount of data and creating a pivot table. But fear not, we have a solution for you. In this tutorial, we'll show you how to use a formula from our book "Excel Gurus Gone Wild" to coerce all dates between two given dates.

Now, before we get started, I have to admit, I always recommend people not to buy this book. It's filled with arcane and bizarre formulas that may not seem useful at first glance. But for those true Excel gurus who love every weird thing about Excel, this book is a goldmine. However, for most people, one of our other books would be a better fit.

In our previous video, we showed you how to use a custom VBA function to figure out which days of the week fall between a start date and an end date. But in this video, we have an even more amazing solution for you. This formula was actually shared by one of our readers on the MrExcel message board, and it blew our minds. So, let's dive in and see how it works.

First, we'll take a look at the start and end dates and see how Excel stores them as numbers. Then, we'll use the INDIRECT function to turn those numbers into an array of dates. This is where the magic happens. By using the ROW function, we can turn those two cells into a huge array of dates. And with the help of the EVALUATE formula, we can see how this array is created step by step.

But that's not all, we'll also show you how to use this formula in a larger range and how it can be modified to fit your specific needs. And of course, we'll remind you that this is an array formula, so don't forget to use CTRL+SHIFT+ENTER to make it work.

So, if you're ready to take your Excel skills to the next level, join us in this tutorial and learn how to coerce all dates between two given dates. And don't forget to check out our book "Excel Gurus Gone Wild" for more bizarre and arcane formulas that may just come in handy one day. Thanks for watching and we'll see you in the next netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Microsoft Excel technique for generating all dates from two date cells.
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.

Table of Contents:
(00:00) Introduction
(00:14) Welcome and Book Recommendation
(00:40) Using VBA to Solve a Problem
(01:07) Introduction to the Formula
(01:25) Utilizing the INDIRECT Function
(01:59) Using the ROW Function
(02:39) Evaluating the Formula
(03:08) Writing the Formula
(03:49) Evaluating the Formula on a Larger Range
(04:02) Changing the Formula for Different Days
(04:18) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Advanced Excel formulas for manipulating dates and weekdays
Creating array formulas in Excel using CTRL+SHIFT+ENTER
Creating arrays in Excel using the ROW function
Evaluating formulas in Excel using the EVALUATE Formula tool
Excel Gurus Gone Wild book review
Explanation of the ROW function in Excel
How to find weekdays between a start date and end date in Excel using VBA
Summing values in an array using Excel formulas
Useful tips and tricks for Excel power users
Using the INDIRECT function in Excel to manipulate dates
Using the MOD function and CHOOSE function in Excel to filter dates by weekday
YouTube video tutorial on analyzing data and creating pivot tables in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152426/

More from this channel