Excel Magic Trick 783: Date Functions & Formulas (17 Examples)
HTML-код
- Опубликовано: 20 май 2011
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download file: people.highline.edu/mgirvin/E...
1. DAY function
2. TEXT Function to get Day spelled out, like "Monday"
3. TEXT Function to get Month spelled out, like "January"
4. MONTH Function
5. YEAR function
6. Last Day in Month: EOMONTH function
7. Last Day in Month: DATE, YEAR, DAY, MONTH functions
8. First Day Next Month: EOMONTH function
9. First Day Next Month: DATE, YEAR, DAY, MONTH Functions
10. Vest Date: EDATE function
11. List of First Day in Month: EOMONTH
12. WORKDAY function -- find workday in future given a certain number of days.
13. WEEKDAY function
14. Last Monday: DATE, YEAR, MONTH, DAY and WEEKDAY functions
15. Formula for Days Between Two Dates
16. Formula for Days Between Two Dates, Inclusive
17. Net Working Days with NETWORKINGDAYS Function
Buy excelisfun products:
teespring.com/stores/excelisf... 
Наука
Mike, I can not thank you enough for your videos. I learned a lot through this about date functions, I love how your videos are to the point, touching all critical points, and simple.
You ROCK!!!
Cool, hamy72 !!! As always, I am glad that you watch and that the videos are helpful!
9 years later and your class is still worth its weight in gold. Thank you for your assistance.
Yes, Date Formulas are still as helpful and the same as 9 years back... Glad they helped, Prague 541!!!!
Nearly 11 years after you posted this I stumbled across this lifesaver! We do daily dated sheets with a mail merge and the Workday.Intl info will now save me SO much time deleting rows with Sundays and our closed holidays. Thank you!!!
I love this video as it simply and fast describes date formulas. There is no messing around. Thank you.
I usually don't comment on your videos, because what I have to say to thank you does not satisfy me. Tireless, knowledgeable and generous instructor as ever! Helpful subject well prepared, vividly described and delivered very nicely. Thank you so much, Mike!
You are welcome for the Excel content, Shiffa : ) Thank you for your kind words. But each time you just leave a small comment like thanks, I understand fully, and it helps promote the videos so more people can learn efficient and fun Excel techniques ; )
Month in text - exactly what I needed. Simple but I wouldn't have been able to think about it myself. Thanks, Nicole from Canada
Mike, the most talented and humble Excel guru ever! Thank you one more time for one more amazing video!
You are more than welcome, Rodrigo!!!!
EXCELlent!!!
WOW! Read my mind in exactly what I needed. Thanks.
The most useful video for date functions and formulae.
The best example and explanation of the day, month and year function compare to the once I have seen, Thanks.
Excellent help! Thank you!!!
Amazing how teach us, thanking you isn't enough for the help you're giving. I've been learning from you from awhile ago.
You are welcome, Ahmad!!! Thank you for your support with your comment, thumbs up and Sub : )
Excellent video. Thank you!!!
Amazing... saved a lot of my time... thank you.
you are a great teacher thank you
Thanks for the awesome video!
Love it! Watched the entire video! Do you have one that shows you how to create a formula if you have multiple columns of dates (for certifications) that expire that will change a cell (even if in a separate column) to True/False based upon the current date?
My god my god, this solves all my problems at once...
Thank you so very helpful and easy to follow- you solved my problem :)
Gosh This is so good！ Thank you！
Awesome, thank you
Perfect!!!!
I hope you are making lots of money from this because you have changed my excel life!
thank you so much for this information
Your all videos are excellent brother. God bless u to give us so much of knowledge without any charges
You are welcome for all the free video lectures, Abhishek : )
Excellent !!!!!
Thank you! 👍😁
Very well done educational video. Clear, quick with no extraneous verbiage. I look forward to using others as I need them. I have been using spreadsheets since the VISICALC era and then with Lotus 1-2-3 and then Microsoft Excel. Every now and again, you know something can be done and watching this video got me my answer very quickly.
You are a cool Spreadsheet Historian then, Karl! Thanks for stopping by and learning : )
@@excelisfun Thanks for the comment. The one thing I that has bothered me about excel is that it cannot distinguish between text and formula. Lotus had that 30 years ago....
@@karl_m3013 Right... And why didn't they keep that!?! We don't know with Microsoft. But there are so many great new things, that it is worth the trouble.
Very Helpful Thank you
Thanks...
you are awesome...
good perfect job
Hello!
I have been looking for a formula, array, vba code to get me a column with the dates for 2019 excluding weekends and holidays. Ive been trying to set up a timeline like calendar and was able to figure out the network days function; however, it didn't include day 31....not sure how to fix this. Any help with this is appreciated. not sure how to attach a sample on here.
thanks!!!
Very Nice Video,,,,,,Thanks
THANKS VERY MUCH
nice format @ 12:01
damn THAT'S GOOD !
Awesome video very helpfull
very good ty
Mike, apart from using the DATE function to find the middle of a month, is there a better way using a different function? I mean, using your video for creating a date list, I just want to create a list for the first 15 days (ie, pay period) and so on. Thanks for the help.
You are a great instructor.d I know a lot of this but it was great to remember in the year 2016 a leap year. I am looking how to extract todays date in the julian number
You're awesome
Finally, I was going mad, when typing ddd in the format boxes and not working! Thank you
This so helpful and enlightening.
A quick question though, is there a way, finding out the next 6th Friday from a stated date?
thank you.
You are welcome!
Hey thanks for the video it was really info laden, i definitely will be looking at your other vids
Glad this videos helps, Jason! There are a lot of videos and full classes. If you need help finding somersetting, just ask : )
thank you
How do I split or break or separate the worksheet so that I can add another one below it? The columns can be different widths in top and bottom worksheets.
Great Mr Mike, but I want to ask a question: does the trick for the last Monday work for any other day or just Monday?, I mean the last Saturday for example?
THANK YOU!!!!!
You are welcome!
looking for help.
every month my works cut off date for overtime if the friday of or after the end of the month (i.e. monday is 30th friday that week is the cut off) i have a pay work out on excel but i cant seem to get the ens date to ALWAYS equal that FRIDAY (it works if the end of the month falls on a friday ) any ideas how to get it to recognise i want the friday AFTER THE END OF MONTH THIS MONTH (i.e. friday the 2nd being a cut off for example)
I had a question. How could I keep the same month and the same year but just have to input the date? such as if the dates I am working with is 6/18 for June 2018, I just want to be able to put 23 in the cell so when I press "enter" it will say "23-June-18" is that possible?
Damn that is magic ...
Hey,
I have a set of data I'm working with that contains dates formatted as yyyymmdd for example 20161001. How do I convert that number to 10/01/2016?
Thanks,
Is there a way to do a month calculation between dates much like the day calculation, but have the end date be the current date and automatically update with the current day?
very nice
Hi
I need to find the overlapped dates in power bi using matrix visual
what Is the Dax that I have to use ?
Thanks!
Thank you for your kind donation, Uma!!!! Data formulas are fun ; )
Wow! That description made my head hurt! LOL! Luckily for you, Excel has a wonderful function called =WORKDAY() and its counterpart =WORKDAY.INTL()
Please get to know them and then re-formulate your question if necessary.
Love your videos🥰
Glad you love them, Caviar! Thanks for the comment and thumbs up : )
Love this tutorial, very helpful. On the vest date... how can I just have it enter a vest year vs a date? I need to tri-annual testing and I want to be able to have a date go out 3-years from the last test date. Help.
Lets say on a 7 day timesheet. Lets say I put 7-21 as the first day how do you format the rest of rest of the days to automatically fill in with out holding the corner of the cell and dragging it to the 7th box? A co-worker told me its possible just dunno how?
I forgot to mention that you must install and load the Analysis ToolPak add-in for this to work!
Thank you so much
You are welcome! Thanks for the support, Koppal, with your comment, Thumbs Up and Sub : )
Awesome videos. Have a question though.
How do I keep days of week locked in a cell, ddd, but have cell update the date of the day when month changes?
So. I have a portrait timetable, 5 weeks stacked on top of each other in rows.
Staff name is down left. Days across top.
Days run Sun to Sat and need to stay locked.
When month changes how do I get the first of month to start on correct day and keep the Sun to Sat in same place?
Eg
Sun Mon Tue Wed Thur Fri Sat remain constant, then when I change month the date appears next to day.
Sun must always be on left.
Any ideas ???
Cheers :)
Any answers here please
what the key you pressed vest date after f4 please quick
I need a template to track my help desk tickets. They will be checked three times a week, Monday, Wednesday and Friday.
I need a way to have the dates move forward, could You please help???
Hi,
I have a slight prob,
How to format a list of short date into long date as a hole..??
Hi, hope you can help me: I need my spreadsheet to highlight contractors whose contracts are about to finish in 3 months time from current date. How do I go about it please? thanks
Woow you have awesome presentation
I am glad that the presentation helps : )
i want to know dates where i have the following condition..suppose joining date is Nov-1-2016.I have to work for 56 days continuously then i will get 28 days holiday then again 56 days work and again 28 days off ..this goes on ..i want to know the formula to calculate the date on which i am get the holidays.from my joining date upto 3 years.
I have learned so many excel magic trick from you and you are a great instructor but i have a problem on the NOW() function is a worksheet function which updates whenever the sheet is calculated, for example on save or open and this is'n't suitable for my purposes . is there any other excel magic trick which saves which didn't overwrite the previous data and continue new
thanks
Im looking to add days but exclude weekends and holidays...any idea how i can do that?
Hi, I am trying to create a spreadsheet for due dates. I would like to update automatically once the task is completed and was wondering what is the best way to do it.give me the best format of excel sheet which is suitable for creating a stock controller sheet
What formula would I use to calculate how many h:mm:ss past a specific time. Here is the scenario, if a store is scheduled to close is 8:00 and they have a grace period of 46 minutes before an infraction occurs. so basically if the close time stamp is after
8:46:00 at 8:46:01 an alert is sent. what formula can be used to let me know
many h:mm:ss past the close time stamp. (example store closed @ 10:35:45 when
they had until 8:46:00)
Is there a formula for autofilling month and year only? i.e. if I enter 12 and hit return, it autofills 12-Jan-18.
Mike, If you've explained this already I apologize. I have 2 columns of dates (from and to dates) that I want to combine into one date range column. Using this formula (=TEXT(H2," mmm d, yyyy")&" - "&TEXT(I2,"mmm d, yyyy"))works fine in most cases but sometimes only part of the date was known so it was written as text or possibly left blank, of course this is where it fails. I've been experimenting but haven't been able to work something out that addresses all scenarios. In the sample below, the 1st and 2nd line work ok but the rest do not. Help please, I have to come up with something to implement on thousands of lines in multiple workbooks so that I can convert to csv files for wordpress upload.
Example
From Date To Date Date Range
September 6, 1916 September 29, 1916 Sep 6, 1916 - Sep 29, 1916
September 1920 October Sep 1, 1920 - October
September 1921 September - Apr 4, 1905
1920 Apr 3, 1905 - Jan 0, 1900
1920 Apr 3, 1905 - Jan 0, 1900
Hi
I need help.
I have a formula in Excel that calculates each day someone is off sick from work and stops counting once you enter a return to work date.
However, I need the formula to discount weekends.
Now I'm aware of the "=NETWORKDAYS.INTL..." function.
The current formula I have enables me to calculate the number of days someone is off sick and effectively stops the clock once you enter a return to work date.
So what formula/function calculates the number of days someone is off sick, stops the clock once you enter a return to work date but also excludes weekends?
Kind Regards
Vividon80
Is there a date formula I can use to anytime a date of the 1st - 15th or the 16th - EOM is put in a cell? If the date input is any date between the 1st and the 15th I want to add EOM,2 If the input date is between the 16th and EOM add 3 EOM plus 5 days. I can do it for specific days but I need it to work based on the range of dates. Example input date is 12 Feb 2017 since the date in the range of 1st - 15th the report due date would by 5 Apr 2017. Help
Hi, just want to ask. How can I sort this kind of date format 06062015 in excel? thanks
How do you input a date then have that date have values to expire in a year, warn you 30 days and 60 days out? Tried conditional but couldn’t figure it out
is there a way to navigate quickly to the column with a today's date?
Hi, so I have following problem. I have begin days and end days, and I need to calculate if there is a weekend (Saturday/Sunday) between them.. Could u suggest me some type of solution? Thx
Is there any possibility to auto update on 30 sheets day/dates/year for 30 sheets? Example sheet1 Sunday 13 February 2016? And Sheets 2 Monday 14 February 2016.
thanks............very helpful
Hi ExcellsFun, Great useful video, I have a litle problem with calculation date in Ms Access, Could you tel me please, how to calculate End of date, if we have starting date and year number. eg: Starting date is 1/1/2017 and give 4 years, When is exactly end date?
Nice
It helps a lot! What app do you use to make this video?
Camtasia
You're awesome. How can I make the date itself change a color once the date is within 15 months?
@EXCELLSFUN WHAT FORMULA DO USE IF I WANT THE NEXT COLUMN TO AUTOMATICALLY WRITE IN (YYYY+2. MM+0. DD-1) . FOR EXAMPLE: C1 = 2018-10-21 AND I WANT D1 TO AUTOMATICALLY SAY D1= 2020-10-20 ? CAN YOU HELP ME TO FIND A FORMULA FOR THAT?
Hello, I have a question: is there a way to display a specific date (i.e. 10/15/2013) but once it passes the 15th of the current month to change to the next month 11/15/2013 and so on?
i have a question what do i do if i put in this formula, text(A2,"mmmm"), to get the actual word but when i do it all i get is the the actual formula the word doesnt show up?
How can I find the date against of the weeknumber.
Ex: week no: 42 Date: ???
please suggest???
The file to download is in xlsm format, and is not ready to be used as is. Is there a particular way to open this file or is there a way to convert it into a usable file? Thanks... BTW, this is great instructional time!!
AOA. sir if i have number of date in 5 or 6 column so what formula i use for selected only one month
Regards My professor ♥
You are welcome, Hazem!!!
Go to File->Options->Formulas->Calculation options->Workbook Calculation and then select Manual
This will prevent your workbook from running calculations until you are ready to run them.
The alternative is to put dummy dates in the cells first.
Hi, I am trying to create a spreadsheet for due dates. I would like it to update automatically once the task is completed and was wondering what is he best way to do it. So for example I have the due date this year and the next due date will be the same but next year so I used EDATE function for that. However I also want my this year due date to change as soon as I state in the cell next to the due date that it is completed to change to the next year.