Excel Hack: Find The Date Of The First Sale In Each Month - Episode 2470

Microsoft Excel Tutorial: Find date of first sale in each month in Excel.

Welcome to another episode of Excel Hacks! In today's video, we're tackling a tough question that was sent in by one of our viewers. The question is, how do we find the date of the first sale in each month without using a helper column? This may seem like a daunting task, but fear not, because I have a solution for you.

In this video, we'll be working with a list of random dates from different years. Our goal is to find the first date of each month without using a helper column. After trying out a few different methods, I've come to the conclusion that a pivot table is the way to go. By creating a pivot table with the dates in the rows and values area, we can easily see the first date of each month for each year.

But wait, there's more! I also want to challenge our formula experts and Lambda experts to come up with a solution using formulas. In the video, I'll be sharing my approach using the XLOOKUP function and conditional formatting. However, I'm curious to see how others would tackle this problem without using a helper column. So, if you have a different solution, please share it in the comments below.

Now, I know some of you may be thinking, "But Bill, your solution still uses a helper column!" And you're right, it does. But in my defense, I did try to find a solution without using a helper column, but unfortunately, I ran into the array of array problem. So, I'm throwing it out to you, my fellow Excel enthusiasts, to see if you can come up with a solution that doesn't require a helper column. Let me know in the comments below.

I want to give a big thank you to Gagan for sending in this great question and to all of you for watching. If you enjoyed this video, please don't forget to like, subscribe, and ring the bell to be notified of future Excel hacks. And if you're looking to improve your Excel skills even further, check out my new courses on the Retrieve platform. These video courses have a complete transcript in multiple languages and allow you to search for specific topics, making it a fast and efficient way to learn Excel. Thanks for tuning in and I'll see you in the next episode of Excel Hacks!

Table of Contents
(00:00) Problem Statement: First Date in each month with a sale
(0:46) Solution 1: Pivot Table
(1:40) Change to Date Grouping in Pivot Tables
(2:32) Solution 2: EOMONTH and XLOOKUP
(3:10) Conditional Formatting based on formula in Excel
(3:32) Solution 3: MINIFS
(4:20) SORT and UNIQUE functions
(5:00) 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:
Array of array problem in Excel
Excel formulas for finding first date in each month
Extracting minimum value from each month in Excel
Finding the first date of each month in Excel
MINIFS function in Excel
MrExcel netcast on finding first date of each month
No helper column Excel solution
Retrieve platform Excel courses
Sorting and removing duplicates in Excel
Using XLOOKUP to find first date in Excel
YouTube pivot table tutorial

Gagan has sales for three years. For each month in those three years, what is the first date that had a sale? For some reason, Gagan needs to do this without a helper column.

My first solution is a pivot table using MIN of Date in the value area.

Note: Excel is changing the label when you group daily dates in a pivot table.

Later, I show a formula using XLOOKUP and "Equal or Just Larger". both in a column or in conditional formatting.

But then, when I try to use MINIFS, I can not get it to work without a helper column, possibly due to the array of arrays problem.

#excel
#microsoft
#exceltips
#microsoftexcel
#exceltricks
#excelformula
#excelformulasandfunctions
#excelxlookup
#microsoft365
#walkthrough
#pivottable
#pivot_table
#excelpivot

[excelisfun]

That is a cool improvement for grouped dates in Pivot!!!!

[wayneedmondson]

Hi Mr. Excel. Cool challenge! My approach was with FILTER, as in: My data goes from B2 to B376. I used FILTER to create an array of only those dates that match the current date on month and year and then take the MIN of that array and compare it to the current date to get a TRUE match for the conditional format. It works on both a sorted and unsorted list of dates, as it relies on the array produced by FILTER vs. the dates on the face of the worksheet. Could make it easier to read with LET, if desired. Fun puzzle to solve! Thanks and thumbs up!!

[nadermounir]

Thank you Mr Excel for this great video.

[Excelambda]

Cool challenge!!✌
First draft, single cell:

[TawneeJohnson]

I have a similar dilemma but I'm trying to find the earliest order date that was open prior to at each week ending interval. If there's a post on Mr. Excel similar to this that analyzes two dates (calendar table week ending), Closed Date and return order date please let me know. It's a challenging one.

[ricos]

Interesting one Bill! I'm not at my PC today, so off the top of my head, perhaps using UNIQUE(MONTH(data range)) then a MINIFS() looking up the data range where month of data equals unique month?

[dravetch]

How about using Power Query...add a column for the beginning of the month. Group by the beginning of month | minimum.

[shoaibrehman]

Sir i would go for pivot table approach.

[Ali_]

Hello Mister Excel, I have a question in Excel. How to perform arithmetic operations (multiplication - division - addition) on numbers long 14 digits??

[billhladik]

Make col A a set of random dates:
=RANDARRAY(100, 1, DATE(2022, 1, 1), DATE(2022, 12, 31), TRUE)

Make col B this formula:
=LET(x, SORT(A1#), cnt, SEQUENCE(COUNT(x)), TEXT(x, "MM/DD/YYYY")&" "&IF(cnt=1, "First", IF(TEXT(INDEX(x, cnt), "YYYYMM")<>TEXT(INDEX(x, cnt-1), "YYYYMM"), "First", "")))

[geoffreyjones]

Wow! A stumped expert seeking advice.
Ok Fans.
Out of the box thinking being sought!
Share your thinking. It may prove to be one of the answers that exist.
To reiterate, A formula, not Pivot Table, that does not use helper cells to accomplish the task.
Who's game?

[guidovilla]

This one seems working to me, and it's pretty short. Maybe I'm missing something?

=MINIFS($B$2:$B$80, $B$2:$B$80, ">="&DATE(YEAR(B2), MONTH(B2), 1))=B2