MS 365 Excel Basics 4: Date & Time Formulas, Functions & Formatting: All The Basics You Need to Know

[@teoxengineer]

Mike hi, 4 series, they're wonderful and so handy. Thank yo so much and I'm looking worward to waiting the 5th tutorial. About last one, how can we add minutes rates into hours calculation if we wanna calculate hours and minutes rates sepereteley as because some companies apply cost of hour and minute is different?

[@excelisfun]

Well since time is hours / 24, as I showed in a brief moment in the video, for whole numbers = (hours+ minutes/60)/24, like 8:30 hours would be: (8+30/60)/24 to get to serial number. For hours and minutes already in serial number form: hours + time, like 8:30 hours would be: .3333333 + 0.020833333 = 0.354166667

[@usmaniqbal1836]

Absolutely Perfect! Mike

[@excelisfun]

Glad you liked it, Usman!!! Since you are already so good with Excel, what new things did you learn in this video?

[@usmaniqbal1836]

@@excelisfun Learned Time Number Formatting and calculation , amazing

[@ankursharma6157]

Gratitude and Best Wishes!

[@excelisfun]

Thank you for the kind donation : ) I am sorry I did not see thing until now, but: : ) : ) : ) : ) : ) : ) : )

[@chrism9037]

This is awesome Mike! Date and Time fun to kick off the week! Go Team!!!

[@excelisfun]

Kick off the week : ) : ) : )

[@HusseinKorish]

That's Amazing Mike as usual ...time and date formulas need a master to explain

[@excelisfun]

Thank you, Hussein!!!!!

[@darylseaton6493]

Thanks Mike!!! Wishing you a most excellent Thanksgiving!!!!

[@excelisfun]

You are welcome!!! Happy T-day to you too : )

[@kiwikiow]

It's a useful date and time formulas video. Thank you Mike :)

[@excelisfun]

You are welcome, ki ki!!! Not too many ghosts in the video : )

[@kiwikiow]

@@excelisfun It's fun to watch though there is no ghost.

[@Excelambda]

Timeless video!!😉✌

[@excelisfun]

Dateless too ; )

[@haihathanh5061]

Great video I've ever seen!

[@excelisfun]

Glad it helps!!!

[@sakisl7739]

I learned so much about date formatting! Many thanks to you!

[@excelisfun]

Many you are welcomes!!!

[@stellaandreeva2444]

Thank you for sharing this amazing content!

[@excelisfun]

You are welcome!!!

[@reng7777]

THE REAL EXCEL GOAT!!

[@excelisfun]

Just having fun with Excel and the rest of the time I am out eating grass lol

[@mansoor_ali_meo]

You are good, very good 👍

[@excelisfun]

Glad to help : ) : )

[@ritikdabral2534]

Hello sir, sir if possible can you make a new series on Power Bi Desktop (Beginner to Advance level) also, it will help us too Thanks

[@excelisfun]

I have 100s of videos on Power BI. Try the best overall Excel / Power BI class that I offer: kzbin.info/aero/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW Videos 4, 10-13 But I would watch the whole class because many wonder Power Query topics are taught throughout the whole class are are integrated with Excel Power Query. Ultimately the most important step in data analysis is data cleaning and transforming to make the correct data model from the data you have and the end result you want.

[@thinktoomuchb4028]

Important stuff to know explained very clearly here. Another thing worth mentioning: b/c of how Excel calculates time, a typed in time may not be equal to the same time derived from certain formulas or by dragging times across cells. That messed me up when trying to visualize employee availability.

[@excelisfun]

I am not sure what you mean. Do you have examples of "typed in time may not be equal to the same time derived from certain formulas"? Or an example of "dragging times across cells" that do not produce the results that you want? I am very curious now...

[@thinktoomuchb4028]

Type in 7:00 am in cell A1 and 7:15 am in A2. Select both and drag down until you have 7:00 am to 5:00 pm in 15 min increments. Starting in B1, type in each time individually. In C1, enter =A1=B1 and send it down. You'll see some times are not equal. A simple formula using SEQUENCE may also not be equal depending on how you write it.

[@excelisfun]

@@thinktoomuchb4028 You said: "Type in 7:00 am in cell A1 and 7:15 am in A2. Select both and drag down until you have 7:00 am to 5:00 pm in 15 min increments." That is true, but is by intention. The reason is that 12 AM must turn to the number 1 in the sequence. When it gets to the second 7 AM, the value is actually 1/1/1900 7 AM, which is 1.29166666666666. I think you are right about formulas too, because when I write this formula to start at 7 AM and increment by 15 minutes: =MOD(SEQUENCE(96*3,,"7 AM","00:15"),1) I also get some tiny differences in the times as the sequence goes on. For example, here are some of the decimals under the 7 AM times: 0.2812500000000000000 0.2812499999999870000 0.2812500000000010000 I guess it has to do how calculations are done in the margins... Thanks for pointing this out, Teammate thinktoomuch4028!!!! Go Team!!!!

[@excelisfun]

P.S. I had to change the title in the video to "All The Basics You Need To Know" because I did leave out many important topics because this was a basics video lol

[@thinktoomuchb4028]

​@@excelisfun Thanks. Doing the drag-down example starting at 7 AM, the first time that doesn't equal a typed-in time is 8:30 AM. Oddly, you get different unequal times dragging down than with a simple SEQUENCE formula. I cannot get SEQUENCE to work without another function such as: =TIME(7,0,0)+SEQUENCE(41,,0,"00:15") =MROUND(SEQUENCE(41,,"7 AM","00:15"),"00:01") It seems dragging and SEQUENCE'ing have there own ideas about when/how to round at 15 places. I'd call it a bug.

[@naveennegi3873]

Guidance on Power Query Videos Hi [Excel Is Fun Team/Mike], I hope you're doing well! I’m currently learning Power Query and noticed you have many excellent videos on the topic. However, I’m a bit confused about where to start. Could you please suggest a playlist or an order in which I should watch your videos to build a strong foundation and gradually advance? Your tutorials have always been incredibly helpful, and I’m excited to dive into Power Query with your guidance. Thank you so much for your support!

[@excelisfun]

The MECS playlist: kzbin.info/aero/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW Teaches everything: Excel Power Query, PowerPivot and Power BI together. But it is not just Power Query. Videos #4, 5, 6, 7,8, 11, 12, 13, 14, 15, 16 all contain Power Query

[@naveennegi3873]

@@excelisfun Thank you so much for responding to my comment and guiding me to the Power Query playlist! I truly appreciate the time and effort you put into creating such detailed and helpful tutorials.

[@sagarborkar1]

Thank you, Sir Mike and hats off to your dedication... 🤠👒 Like all your other videos, this one was also amazing, especially the Time Calculations, which I always found challenging, and You made it so simple to understand...👏 While some monetize similar content through courses and earn thousands of dollars, but here you generously share your knowledge with complete dedication, solely to assist the community...🙏 Once again, Thank You; You are truly Awesome...👍

[@excelisfun]

Thank you for your kind words : ) For almost 17 years may goal is to give education away for free that uses good story telling to make complicated things less complicated. The crazy thing about the world is: entities like Microsoft MVP program and Google search do NOT recognize "Free" or "Quality" as criteria. This is why a video that is free and is better than paid content has only 2,500 views. To be fair, it is my bad marketing also... But I choose to spend my time creating good stories rather than good marketing,

[@sagarborkar1]

​@@excelisfun Indeed, it's all about business; SEO marketing, paid video promotion, and sponsors of those video ensures their videos trend and appear first in specific searches. It's about using algorithms to one's advantage for a higher view count. We know you are capable of doing all these things, but your content creation serves a different purpose, which we all Appreciate. 🙏 I have always admired your enthusiasm and the joy you derive from teaching. There's no need to be disheartened even if your video gets only one view because if that one person was able to learn and enhance their skills and productivity, then that is your true Success. The universe has its own ways to respond. Just as every action has a reaction, your selfless efforts will surely attract many blessings. 😇

[@benaissachokri6015]

❤❤❤❤❤❤

[@excelisfun]

: ) : ) : )

[@jamierogers294]

I also have a forbidden love for the "hidden" DATEDIF function.

[@excelisfun]

O, Yah!!! What a lovely Lotus 1, 2, 3 function that they let into Excel through the back door : ) P.S. I had to change the title in the video to "All The Basics You Need To Know" because I did leave out many important topics because this was a basics video lol

[@dziadeck47]

Can't understand why it's a hidden gem for decades, despite no better official alternative

[@excelisfun]

@@dziadeck47 I think it is because Lotus 1, 2, 3 invented it, and MS kept it for backward compatibility. And because they do not offer documentation, and as a result it seems like a mystery, or hidden gem.

[@johnborg5419]

Thanks Mike. :)

[@excelisfun]

You are welcome, Formula Guy John!!!!

[@kasvinimuniandy4178]

That's really cool

[@excelisfun]

Glad you like it!!!!

[@jayhinytzke3269]

Mike: how does one account for Daylight Savings Time change in the fall and spring. I created a spreadsheet that I use for a logbook and can't figure out how to account for the time change in the spring and fall. I track the time I spend in the hotel and also the time working from home and back to home the next day. For example, I might go to work leaving from home at 0800 hours on Saturday and get done at 1830 hours on Saturday. Then I spend 12 hours in the hotel. I then head back home on Sunday at 0630 and get done at 1700 on Sunday. I either have to manually adjust the time in the hotel, or the time working depending on where I was when the time changed. Is there a way to account for this automatically without having to manually adjust? Thanks, in advance.

[@excelisfun]

I have never had to deal with that. I do not know of an automatic way, and as I think through it, I would try to add 1 or subtract 1 on those two days in the year.

[@jayhinytzke3269]

@@excelisfun I figured that would work, but I was hoping for an automated solution. Thank you for the response.

[@excelisfun]

@@jayhinytzke3269 I guess there could be an automated solution, but the only thing I could think of is a lookup table with time change dates. Because somehow, a formula or other solution must be informed of what days the time changes.

[@anujpradhan7516]

Hi, would you do some tutorials regarding retail floor especially, breaking down of yearly given budget (YTD) into Monthly Budget, Weekly and Daily and more analysis?

[@NicolasChanCSY]

Such an awesome video detailing most if not all Excel date/time functions! This video would have been even better if Americans follow ISO 8601 or YYYY-MM-DD to represent dates, like the rest of the world.

[@excelisfun]

Sorry for that omission : ( I will cover yyyymmdd conversion in a later data analysis video : )

[@excelisfun]

P.S. I had to change the title in the video to "All The Basics You Need To Know" because I did leave out many important topics because this was a basics video lol

[@huyhan6303]

Excuse me sir, but I did a recheck with your first video, you show us the menu or the progress plan of 10 videos, I noticed: 1. Lesson 2 you mentioned in your first video include table, pivot table.... (different with actual lesson 2) 2. Lesson 3 in the menu is actual lesson 2 ... May I ask will we have lesson about tables, pivot tables... etc And yes, I viewed all your lesson so far :D

[@excelisfun]

I have already changed the number of videos to maybe 13 or 14. PivotTables will start in about #8. Stick with this class, but since PivotTables have not changed much, if you need that skill now, use my older Excel Basics class and watch videos #4, 5 and 20. Here is playlist: kzbin.info/aero/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k

[@huyhan6303]

@@excelisfun Weow, I didn't know you already have the basic course I'll stay tuned waiting for new course then :D

[@excelisfun]

@@huyhan6303 I already have 3,700 videos and over 100 playlists. Been doing this since 2008 at KZbin. Are you subbed? If yes, how have you not seen the intro video? Here is into that intros : ) = > kzbin.info/www/bejne/bmK6ZniQn9yHp80 Here is home page: kzbin.info

[@huyhan6303]

@@excelisfun nah, just recently viiewed all your 4 basic excel courses + Power Query (as I googled to find some solutions) I'm pretty new here :D

[@Animesh_Goyal]

Hello sir, I have a query about Excel. I have data in two sheets: the first sheet is the main sheet, and the second sheet contains month-wise exchange rates in columns. In the main sheet, I need to look up rates based on the month in specific columns. However, the month's columns in the main sheet are non-adjacent - for example, January is in column C, February in column F, March in column I, and so on. I want to fetch the exchange rates from the second sheet using an XLOOKUP formula. The problem is, when I copy the XLOOKUP formula across columns, the lookup array shifts based on the column difference. I want the formula to adjust only one column at a time. Is there a way to do this with XLOOKUP without changing the column headings in Sheet 1 to match those in Sheet 2?" Thanks in advance!

[@excelisfun]

I do not follow what you say well enough to know exactly what you are asking. But, Lookup array shift? That sounds like references are not locked. I am 100% sure that if I understood, I could fine a way. January is in column C, February in column F, March in column I? Maybe you need to remove empty columns or filter the lookup thing to remove empty columns. Column headers not mothing? That is a terrible model setup. In all of databasing and data analysis, almost all solutions depend on column headers matching...

[@Animesh_Goyal]

Sorry for the confusion. Let me explain it with more details and accuracy. I have data in two Excel sheets; let's call the first sheet "Depreciation Calculation" and the second sheet "Foreign Currency Rate," which contains various countries' rates converted into dollars. (In 2nd sheet , column A has various countries' currency abbreviations EUR, INR, CAD, etc. and from col B onwards their conversion of rates for each month from 2021 to 2024). In the "Depreciation Calculation" sheet, column A contains various types of assets, and column B provides their original currency denomination. Some assets fall in Euro, some in GBP, and so on. We need to perform calculations in dollars, so in column C, we pull the foreign currency (FC) rates from the "Foreign Currency Rate" sheet. This sheet has been provided by the client, with all other currency rates converted into dollars for each month from 2021 to 2024. Now, coming back to the first sheet, we pull the January rates into column C using XLOOKUP (with the lookup value in column B and lookup array in column A in sheet 2). In column D of the "Depreciation Calculation" sheet, we convert the LC amount into dollars to calculate depreciation, and we perform other calculations in subsequent columns from D to H. In column I, we need to pull the rates for the next month (February) from the "Foreign Currency Rate" sheet. Here is the issue: in our XLOOKUP formula, the lookup value (e.g., EUR, GBP, CAD) is fixed, but the return array is not. When I copy the formula from column C to column I to fetch the rates for February, the return array from the "Foreign Currency Rate" sheet shifts incorrectly based on the number of columns skipped in the "Depreciation Calculation" sheet (as is normal in Excel). On the other hand, in the "Foreign Currency Rate" sheet, the February rates are just one column away from the January rates (e.g., if January rates are in column B, February rates are in column C, and so on). I want the formula to change its return array by only one column difference, no matter how many columns I skip in the "Depreciation Calculation" sheet. Please note that using INDEX and MATCH or VLOOKUP with MATCH is not a great idea, as we cannot change the format we use to calculate month-by-month depreciation.

[@excelisfun]

@@Animesh_Goyal You would need to send me a workbook with an example for me to fully understand. It is not your explaining, it is that I am slow and am not "seeing" the whole picture.

[@excelisfun]

@@Animesh_Goyal You can send a simplified example that is exactly parallel to the real workbook and say what the goal is and what the expected result should be. That way I can back into the logic if needed. excelisfun at gmail

[@Animesh_Goyal]

@@excelisfun No sir. I am sincerely Apologize. I will send the workbook.

[@GeertDelmulle]

My date granularity right now is “weeks”, so I’ll have to be a little smart about it. Luckily we have a function like ISOWEEKNUM, but apart from that, not many week-related date functions in Excel.

[@GeertDelmulle]

PS, one of the functions I need is StartOfWeek - I used a recursive LAMBDA for that (our weeks start on a Monday). Do you have another solution for that?

[@excelling6955]

@@GeertDelmulle , StartofWeek - if week starts on monday, then from what i understand - the monday of the week in question would be the start of the week. For that you might want to try A2-(Weekday(A2,11)-1) where A2 has the date for which the start of the week is needed

[@excelisfun]

Teammate Geert!!!!! : ) : ) It is funny, I just have never had to deal with week units of time in my whole career. @excelling6955 seems to have a good one with: A2-(Weekday(A2,11)-1)

[@excelisfun]

Thanks Teammate @@excelling6955 !!!! Love that formula : ) : )

[@excelisfun]

P.S. I had to change the title in the video to "All The Basics You Need To Know" because I did leave out many important topics because this was a basics video lol

[@vijayraghavanr1]

First to comment 👍

[@excelisfun]

I award you the first place Trophy!!!!! : ) : )

[@JONRESTREPO]

Hello; Why can't the custom number format for dates in Spanish format my date "ddd" so that my result is "Vie" or the day in 3 letters...?

[@excelisfun]

Mayeb this can help: Steps: 1. Select the cells: Highlight the cells containing the dates you want to format. 2. Open Format Cells: Press Ctrl + 1 (or right-click and choose "Format Cells"). 3. Choose Custom: In the "Format Cells" dialog box, go to the "Number" tab and select "Custom" from the "Category" list. 4. Apply the format: In the "Type" box, enter the following: ddd "de" mmmm yyyy (or a similar format based on your desired output). ** ddd represents the abbreviated day of the week (e.g., "Lun" for Monday). ** "de" inserts the Spanish word "de" (of). ** mmmm represents the full month name (e.g., "enero" for January). ** yyyy represents the four-digit year. 5. Click OK: Click "OK" to apply the custom format. Example: If you want to display the date "2023-10-27" as "Vie 27 de octubre 2023" (Friday, October 27, 2023), you'd use the format ddd dd "de" mmmm yyyy.

[@JONRESTREPO]

@@excelisfun Thankss its Working!!!

[@tariqaziz783]

Why always Cntrl + Enter , not just ENTER....?

[@excelisfun]

When my goal is to enter the formula and immediately do something to the cell, I use Ctrl + Enter. Although I used Ctrl + Enter gratuitously a couple of times in the video, most of the time I used it with intention because in the video I immediately did something to the cell with the formula and I did not want to hit Enter and then arrow up.

[@shafiullahharifal9527]

Hello sir how are you. Sir how can I format a time like “Jan-01 to 31”.

[@excelisfun]

A time? I have no idea how to show a time as a range of dates except to apply a text number format like: “Jan-01 to 31” on top of a time serial number like 0.333333 , which to me would make no sense... What time do you have? What is the criteria applying such a text formatting?

[@shafiullahharifal9527]

@ Sory a date. Sir I prepared a timesheet and I wished to put the date in a specific cell by data validation and when I click a month it could change the days automatically. But the date format should be like “Jan-1 to 31”. Thanks sir

[@MOHAMEDMOSTAFA-bv4ro]

any courses available sir

[@excelisfun]

I am not sure what you are asking. Can you re-state?

[@MOHAMEDMOSTAFA-bv4ro]

@@excelisfun A paid online course focused on a specialized level

[@excelisfun]

@@MOHAMEDMOSTAFA-bv4ro Me? No. My goal for 17 years at KZbin is to give everything away for free at KZbin. I have many, many free courses here at KZbin. Here is my intro video: kzbin.info/www/bejne/bmK6ZniQn9yHp80 or just watch the auto play video on my home page: www.youtube.com/@excelisfun

[@MOHAMEDMOSTAFA-bv4ro]

@@excelisfun I would like to sincerely thank you for your kindness, effort, and valuable knowledge. Your contributions have made a significant impact, and I wish you all the best in everything you do. May you be blessed with good health and continue to be a shining example for educators around the world. May your deeds be recorded in the balance of your good deeds after this life

[@excelisfun]

@@MOHAMEDMOSTAFA-bv4ro Well, thank you too for offering to pay for a course : ) But I like free. Thanks you also for your very kind words!