Date & Time Formulas & Functions in Excel Worksheet and Power Query - 365 MECS 05

Great video and yes it was EPIC!

Can we get a power bi series that we can watch and take the certification examination? Thanks for what you do

About leap years. There’s an internal bug in Excel as they counted 1900 as a leap year. But, you say, 1900 is divided by 4 so it’s a leap year, right? Well, indeed, 1900 is divided by 4 but to be a leap year, a centennial year must be divided by 400 and 1900 is not, while 2000 was. The bug is corrected internally and it might as well be intentional to keep compatibility with Lotus-1-2-3 where it was also.

I always click on ads on your videos❤

Great

❤

🎉

A minor nit pick, for the fiscal year formulas if the date is prior to the fiscal year end date, the calendar year is the fiscal year, otherwise it is the next calendar year.

Thank you very much for another good teaching.

Thanks, Mike, It's a great one, But I want to check how to get the second Sunday of Every Month on the sequence.

For night shift problem you can also use this fprmula: D7-C7+IF(D7<C7,1,0))*24

Just placed my order on Amazon for "Microsoft 365 Excel: The Only App That Matters: Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight." Woo Hoo!

Amazing!

hardworking as usual

I need your help, please can you provide me your fb id?

Hands down, you have the best Excel videos out there! Thank you! If you happen upon my comment and feel like responding, I've been experimenting with creating a three column schedule that cascades based on the time entered into the first column and it's duration. First column is time, second is activity, and third column is estimated duration of activity (hh:mm). What's the best way to populate each new activity's start time based on the previous activity's start time plus the previous activity's duration. So if I started at 7:00 AM with a 45 minute task, the next row's time would automatically populate with 7:45 AM and so forth. I don't expect an answer, but thought it couldn't hurt to post. :-) Thanks again for the inspiring videos, Mike!

Hi is there a formula or PQ way to change any transactions time that come between 12:00:00 AM and 7:59:59 AM To be 8:00:00 AM which is the start of working hour.

It's a great video. I am not familiar with time formulas. I'm glad you did this video. Thank you Mike 🙂

i love u

Mike,
Thank you for sharing this beautiful tutorial that no one can obtain such knowledge
Everyone should have this carefully prepared study in their archives.
What I want to learn is, when given a month name, how can we calculate the number of months corresponding to this month name?
For example, in a data with the month "February", how can we convert the name of the month to the number "2"?
Excel can calculate the opposite solution like, =TEXT("2/28/2022","mmmm") = February
Thank you

How many more videos do we have for this MECS playlist? -Team Mike

Hey Mike just been working through Slaying Excel Dragons, just wanted to check if the advanced filter on sheet AF(8) exercise is correct because when I try to extract the unique names and paste using advanced filter to the extract sheet Excel won't allow me to do it stating " the data must be filtered".
Not sure if something has changed?

Thanks in advance for your help.

Boom!This Was One Awesome Super Fun Refresher Class Happy Days...Thank You Mike :)

Amazing Tutorial as usual ... date and time functions were tricky for me ...Thanks Mike

Thanks Mike. More Excel magic! 😀

Really an epic Video, could learn a lot again. Go Mike, go! 🏆
Hope you are back to normal again. Stay safe and be strong!

Hi Mike, when I entered the Text function for the month in the table, the data was showing January throughout the entire table. How do I change it to reflect the exact month?

Thanks Mike!!! (end-start+(end<start))*24 very cool way how to do it although I always used the Mod function. Great Video!!! and thanks again!! :) :)

This video is another whole subject let's call it "TIME"ology by Mike Girvin

Hi Mike,
How are you holding up?

Also with the PQ solution, you used PQ ribbon, which is fine but it also creates steps for each column added.
Im typing this from memory with no access to PQ but:
= Table.addColumn(Your_step, "Record", each [
Column1Name = Calculation1,
Column2Name = Calculation2,
Column3Name = Calculation3,
Column4Name = Column3Name + (Column2Name - Column1Name)
])

and now either in new step or engulf formula above with Table.expandrecord i belive.
So in general thi will make a clear code without x times table.addColumn which from my expierience is clearer to understand if you have more then 2 columns to add in succession.

Thanks Mike for this EXCELlent video

Thanks for sharing your knowledge, you help a lot of people. Continue sharing your blessings ❤️

Now this is fast, considering what happened to you last week
Thank you, and Stay strong!

Thanks Mike, did not watch it yet but is excited to as I really need to get a grip on PQ. Here in South Africa our Fin Years and periods are different from the Northern hemisphere and I hope this will help me build custom periods. Thank you for your dedication. Regards Lester

This was fantastic Mike!! Date and Time can be a little tricky so this was super helpful!

You are a master 👍👍👍

Mike, thanks very much for this one. I’ve been looking forward to it. One topic eludes me though… I do archival work on old collections for family history research and I am constantly tripped up when working with dates before 1900.

Do you have, or know of a good video/blog for effectively dealing with dates prior to 1900 in either Excel or PQ? As they always format as strings, I can’t do date math or sorts with them, especially when they are included with other dates after 1900. Surely someone else has solved this… or, I’m missing something easy? Everything I’ve come up with so far is a messy hack.

absolutely amazing. you make things look so simple! Thanks so so much for making all of us better excel users. Got a question for you. Can you work with tenths of a second or milliseconds? Imagine a sports chrono (last minute usually displays seconds and tenths of a second) or just measuring how quick a code is. Both situations require operating below seconds. Is it possible? Any feedback? Got your new (must-have!!!) Microsoft 365 Excel book and couldn't find it in there either. Sincerely appreciate your videos and your positive attitude. Thanks so much and all the best

Great video!!
Regarding quarters, fiscal quarter/years, seems the perfect scenario for a simple lambda function alternative to PQ, all in one that adds also the versatility, for other countries, to choose a different starting month of fiscal year, like 7 or 10 instead of 4

QTR(ar,[qt],[sm]) where:

ar: dates array, (could be single value, cell reference, column/row vectors or even 2D arrays)
[qt]: quarter type argument, (one of these values "q", "qy", fq", "fy", "fqy" )
[sm]: start month of fiscal year
- if sm is omitted, default value is 4, if we need 7 we input 7

qt quarter type argument values:
- if qt is omitted, default value = "qy" => this format ex: 2022 Q4 (always year first, for sorting versatility)
"q" ,calendar quarter => format ex: Q4
"qy" ,calendar quarter and year => format ex: 2022 Q4
"fq" ,fiscal quarter => format ex: FQ4
"fy" ,fiscal year => format ex: FY 2023 (fiscal year 2023-24)
"fqy" ,fiscal quarter and year => format ex: FY 2023 Q4
If none of the above function will return the value for "fqy"
Could have use numeric values instead of text, but with letters is easier to remember

=LAMBDA(ar,[qt],[sm],LET(
s,IF(sm,sm,4),a,IF(ar="","",ar),
m,MONTH(a),y,YEAR(a),
q,"Q"&MONTH(m&0),qy,y&" "&q,
fq,"Q"&MONTH(MOD(m-s,12)+1&0),
fy,"FY "&y+(m>=s)-1,
fqy,fy&" "&fq,
IFERROR(SWITCH(qt,0,qy,"q",q,"qy",qy,"fq","F"&fq,"fy",fy,"fqy",fqy),"")
)
)

If dates array has blanks, empty strings, text or errors will be ignored and function will return empty strings instead.

Great as always
I just wish there was guidance on e.g time difference in months (how many months have passed...) - is there a replacement for a long abandoned by Microsoft (however still working) Datediff formula at last?

Thank you for this new video.. love all your uploads.. I'm learning a lot about excel from you! More power and God bless!😀

Badness can delay but cannot DEFEAT!!! : ) : )

Mike, that was comprehensive again! Thumbs up!

Love this refresh series. Such a systemtical learning path. Have to change my plan for memorial day shopping 🤣🤣🤣

Surpreendente... Valeu.

Awesome...... Thanks Mike. As detailed as ever.