You are bright and easy to follow. I'm already applying these functions in production. Plus, I already DWNLD the Analytics guide today. Will see all your vids. Thank you
'Dated if' has been there for so long in circulation, but you read the developers mind who created this - he/she called it 'Date-Dif' 👍👍 As that is what this built-in function is for 😁
It is DATE-DIF; it was created to provide compatibility with Lotus 1-2-3 and calculates the difference ("dif") between two dates by day, month, or year. Not sure why it is being called a "NEW" formula here, but is has been known as a "secret" formula for a very long time. I've been using it for at least two decades.
DATEDIF (pronounced DATE-DIF, as it is calculating the difference between two dates by day, month, or year) is not a new function; it's been available for years. The reason it does not expand to show formula parameters like most other formulas is that it was provided as a "secret" formula in order for Excel to be compatible with Lotus 1-2-3 (that right there should tell you how old it is). It is a great formula though, so thanks for sharing.
To extract the Emails, you can use Ai.Extract formula better as you explained it in previous video. I would like to thank you, i learn from you a lot ❤❤
Dear Kenji, Spilled formula and with a header it is better: =REDUCE({"First Name","Last Name","Age","Outcome"},B3:B14,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,{" ",", "},,1)))) And TEXTJOIN with the right delimiters is better too: =TEXTJOIN({" ";", ";", "},,C3#) 🤗
Using groupby how can I get the latest date in column F,? Say, I have a list of employees leaves, and I need to get the latest leave taken for each employee
Hey I'm using microsoft365 (but I also get early access to some formulas before they're released to the general public). Many of these (except regex i think) should be available in microsoft365 already
From my understanding the new functions and features they roll out are generally for Office 365. That is one of the advantages of the subscription service.
DATEDIF is ancient history! Excel > Formulas and functions > More functions > DATEDIF function Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios. Please see the known issues section of this article for further details.
Datedif is not new, it is just like Excel’s illegitimate child that came to light, It is so neglected that there is no helper text/explanation for the arguments, they don’t even bother to standardize the same keyword in VBA (Datediff), and the arguments also in the wrong order. It is a formula/command that would be useful for a newbie, yet only be told much later on when it is no longer in need.
Guys! Today I noticed something supper odd. And now I wander was it always like this? Here's the issue/bug: in both Excel and GSheets the COUNT(range) and COUNTIF(range, "") formulas are counting the cells even if they have formula that outputs "" Blank results! WHATA HACK IS EVEN THAT?!
🚀Get the FREE Data Analytics Guide from Hubspot: clickhubspot.com/1c5
Beautiful, thank you Kenji.
The TAKE + SORT + CHOOSECOLS is very helpful.
You are bright and easy to follow. I'm already applying these functions in production. Plus, I already DWNLD the Analytics guide today. Will see all your vids. Thank you
Kenji, your videos are brilliant! Crisp and absolutely easy to follow👌🏽🙏🏽
Nice demonstrations of some amazing new features. The capability of Excel is now unbelievable.
great video very informative , this helps my job a lot, thanks and greetings from Timor-Leste
These combination of Take, Sort and Choosecol can be use as alternative of GSheet Query on Excel,
A real game changer on Excel formulas,
Thanks!
Thanks for watching! :)
I still think the function should be called "DATE-DIF," not "DATED-IF"
I assume Kenji is dating someone. 😂
Agreed! I do call it DATE-DIF 😊
I am with you, there is no condition attached to the function, why would someone say dated if 😂
'Dated if' has been there for so long in circulation, but you read the developers mind who created this - he/she called it 'Date-Dif' 👍👍
As that is what this built-in function is for 😁
It is DATE-DIF; it was created to provide compatibility with Lotus 1-2-3 and calculates the difference ("dif") between two dates by day, month, or year. Not sure why it is being called a "NEW" formula here, but is has been known as a "secret" formula for a very long time. I've been using it for at least two decades.
Good to know, that the TEXTSPLIT function can take a list of delimiters as an argument and not only one. That was new for me👍
Really your taking us to super levels, than and many thanks
DATEDIF (pronounced DATE-DIF, as it is calculating the difference between two dates by day, month, or year) is not a new function; it's been available for years. The reason it does not expand to show formula parameters like most other formulas is that it was provided as a "secret" formula in order for Excel to be compatible with Lotus 1-2-3 (that right there should tell you how old it is). It is a great formula though, so thanks for sharing.
Thank you for this. I am wondering which tools you are using for making this wonderful videos.
To extract the Emails, you can use Ai.Extract formula better as you explained it in previous video. I would like to thank you, i learn from you a lot ❤❤
The AI add-in was fantastic.
Really great tricks!! Thanks!!
Thanks. Can you do a video in comparing 1st and 2nd and what is the easiest:best way to extract difference?
Dear Kenji,
Spilled formula and with a header it is better:
=REDUCE({"First Name","Last Name","Age","Outcome"},B3:B14,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,{" ",", "},,1))))
And TEXTJOIN with the right delimiters is better too:
=TEXTJOIN({" ";", ";", "},,C3#) 🤗
Using groupby how can I get the latest date in column F,?
Say, I have a list of employees leaves, and I need to get the latest leave taken for each employee
Is regex under beta version? Coz as an employe with subscription to ms365 I am no getting the =regex while using. Can anyone help how to enable
correction : "Really you are taking us to super levels, thanks and many thanks"
Hello Kenji, please share Excel version you are using with those updated formula.
I need that .exe file to be on same page with you
Hey I'm using microsoft365 (but I also get early access to some formulas before they're released to the general public). Many of these (except regex i think) should be available in microsoft365 already
It seems we are not even using 10% of excel functions. Thanks for the info
will all this work in MS Office Home or only for MS 365?
Hey I'm not too familiar with MS Office Home tbh so I can't comment. I'm currently on MS365
From my understanding the new functions and features they roll out are generally for Office 365. That is one of the advantages of the subscription service.
do you know if the AI one will become a part of Excel (not an addin) at some point?
I don't know tbh!
I Still don´t have these functions and my account is an insider one. What can I do to have them? Already updated excel several times.
I too don't have the choose cols formula
My download and your video file did not match, I had country and units sold columns
Handsome kenji
thank you lol hopefully the video was useful XD
Sir from where should I download free updated MS Office for long time
Just wow..
create a regex to help me identify if there are numbers in a list of
My excel not showing new formulas pls help me to get results
DATEDIF is ancient history!
Excel > Formulas and functions > More functions > DATEDIF function
Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios. Please see the known issues section of this article for further details.
Datedif was canceled as official formula because, in verry specific conditions, it may give an incorrect result
Alex Megos 👀
Hahha glad you noticed XD
What version of Excel are you using?
2021
2024
Microsoft 365 or
Office 365?
Hit the Tab key thereee.
Column A
Chemistry
Biotech, Chemistry
Physics
Column B
Q1
Q3
Q1
I want pivot table result as below
Chemistry Q1 (count 1) Q3 (count 1)
Biotech Q3 (count 1)
Physics Q1(count 1)
Datedif is not new, it is just like Excel’s illegitimate child that came to light,
It is so neglected that there is no helper text/explanation for the arguments,
they don’t even bother to standardize the same keyword in VBA (Datediff), and the arguments also in the wrong order.
It is a formula/command that would be useful for a newbie, yet only be told much later on when it is no longer in need.
Guys! Today I noticed something supper odd. And now I wander was it always like this? Here's the issue/bug: in both Excel and GSheets the COUNT(range) and COUNTIF(range, "") formulas are counting the cells even if they have formula that outputs "" Blank results!
WHATA HACK IS EVEN THAT?!
Yeah, including chatgpt into excel with valuable company data.... Not going to happen.... 😂
4:24
=C3& " " & D3 & "," & E3 & "," & F3
Result will be - Kylee Townsen,56,Pass