25 Date and Time Functions in Excel You Need to Know
Вставка
- Опубліковано 5 жов 2024
- Learn all 25 date and time functions in Microsoft Excel.
1. Dates and Times in Excel 0:24
2. TODAY function 3:24
3. NOW function 4:18
4. YEAR function 4:59
5. MONTH function 5:44
6. DAY function 6:20
7. HOUR function 6:55
8. MINUTE function 8:06
9. SECOND function 8:36
10. EDATE function 9:12
11. EOMONTH function 11:08
12. DATEVALUE function 12:48
13. TIMEVALUE function 14:29
14. WORKDAY function 15:19
15. WORKDAY.INTL function 18:01
16. NETWORKDAYS function 19:43
17. NETWORKDAYS.INTL function 21:43
18. DATE function 23:29
19. TIME function 25:34
20. WEEKDAY function 27:05
21. WEEKNUM function 28:36
22. ISOWEEKNUM function 30:17
23. YEARFRAC function 31:10
24. DAYS function 32:45
25. DAYS360 function 33:26
26. DATEDIF function 34:42
Check out my full courses and ebooks here:
👉 www.howtoexcel...
DOWNLOAD any example workbooks here:
📖 www.howtoexcel...
SUBSCRIBE & get my 3 FREE eBooks.
📧 www.howtoexcel...
CONNECT with me on social:
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
FINALLY! Someone who explains dates and time in Excel properly! Congrats!
Thanks Paul!
Timestamps for copying into Description
1. Dates and Times in Excel 0:24
2. TODAY function 3:24
3. NOW function 4:18
4. YEAR function 4:59
5. MONTH function 5:44
6. DAY function 6:20
7. HOUR function 6:55
8. MINUTE function 8:06
9. SECOND function 8:36
10. EDATE function 9:12
11. EOMONTH function 11:08
12. DATEVALUE function 12:48
13. TIMEVALUE function 14:29
14. WORKDAY function 15:19
15. WORKDAY.INTL function 18:01
16. NETWORKDAYS function 19:43
17. NETWORKDAYS.INTL function 21:43
18. DATE function 23:29
19. TIME function 25:34
20. WEEKDAY function 27:05
21. WEEKNUM function 28:36
22. ISOWEEKNUM function 30:17
23. YEARFRAC function 31:10
24. DAYS function 32:45
25. DAYS360 function 33:26
26. DATEDIF function 34:42
Thanks again, I finally got around to adding these in!
Would be super useful to do a tutorial on Time to decimal Time. EG 12:30 (12 hours 30 minutes = 12.5 hours), this is such a difficult function, even harder when more than 24 hours, please do a lesson on this, loads of people need it. Excellent video as normal
Could that be anymore prettier!!!
Simply awesome & professional 👌
a last I get something better than usual.... thank you man..
😊
Hi John.. excellent.. very complete. Worth mentioning is the option of the weekend day "mask" for the two .INTL functions. where you can specify weekend days as a "1" and non weekend days as a "0" in a sequence of 7 numbers between quotation marks. So, "0000000" would be no weekend days; "1000000" would be one weekend day of Monday; "0101000" would be two weekend days of Tuesday and Thursday.. and so on. This feature can be useful in certain circumstances or and situations. Thanks for the videos and good learning at your channel. Thumbs up!!
I definitely would have mentioned it if I had known it but it's new to me. That's a cool tip. Thanks! 😀
Thanks John for the tutorial.
Clear, concise and professional tutorial - thank you:-)
You're welcome 🙂
This had to be done by proffesional,appreciated.
Thank you, this is exactly what I needed.
Used yearfrac right away. Thanks
Good stuff.
I still haven't come across a use for ISOWEEKNUM or DAYS360 yet. 😂
Great Tutorial! Really Well Explained..Thank You John :)
Though it’s good practice to manually close a function with a right parentheses, you can actually type tab or return if Excel expects no other mandatory arguments. Type now( + enter, for instance. Optional arguments will use default value. Not an option in complex formula with nest functions, though.
Yes, I press enter usually omit the end parentheses.
It is a good practice to close parenthesis as it builds up a habit which will be crucial when making nested functions. Also, not closing the parenthesis will not work for mobile version of sheets.
Very nice thank you for the info
Excellent
What happens with Time Value when you have a text written time that could be an or pm? E.g. 11:30. As no AM or PM is given does excel automatically assume it is the 24 hour clock.
Thank you.
really helpful
Easy trick to always get the correct last day of the month is using a combination of the EOMONTH and DATE formulas. The day should not be filled in the DATE formula. It works as follows for 29/02/2020: =EOMONTH(DATE(2020,2,),1).
The trick is that not filling out the day number in the date formula, Excel interprets it as the last day of the previous month (= day zero). By then combining it with EOMONTH, you have an easy way of always getting the correct end date of the month, whether the month has 28, 29, 30 or 31 days.
Great tip! Thanks!
How do I customize the WORKDAY function if my work week contains Saturdays and Sundays on Mac?
You can combine date and time as a time stamp by typing ctrl semicolon then a space and ctrl Colon. Equivalent of now() but as a number, not a formula.
Good tip!
@@HowToExcelBlog For sake of completeness, on the macOS version of Excel AND an Azerty Keyboard, current time is given by both command ⌘ + semicolon ; and command ⌘ + colon : and current date is given by both control ⌃ + semicolon ; and control ⌃ + colon : Maybe because colon and semicolon are on 2 different keys, while they are on the same key on a Querty keyboard? Under Excel for Windows and an Azerty keyboard, it's the same as described in the video with ctrl key only, just on 2 different keys. Can't verify on a macOS version of Excel and a Querty keyboard, though.
I found this video because I'm trying to figure out how to compare calculated elapsed time which is a value of "28:26:20" against a set threshold of 24 hours. when using comparison operators it seems to not recognize the hard value "24" as hours. This can't be that difficult but I can't find an answer anywhere
Hi guys , what is the difference between weeknum and isoweeknum function? Couldn’t get what is the diff? Can someone explain more? Thanks
ISO is based on a weird weekly calendar system that can have 53 weeks instead of the normal 52, and you can likely safely ignore this function for your entire Excel career without worry. 😀
Hello I need to calculate time elapsed between date and time of a task being received and completed. I need to exclude weekends and it must only calculate time within 08h00 and 17h00 = can you please assist?
Does anyone know how to create a timezone sheet in excel where i enter a time of a certain time zone and excel shows me the time in different time zones. Any tutorial would help
How can i get one cell data update information to other cell where I can find last updated date and time automatically?
how to find in a list the dates of today and after, and display them in a cell?
Hi. How can I compute this.
If the start date si June 3 2021 and end date is March 11 2023
How many months and days? But I need it was converted as months
The answer should not 21.8 because it is literally 21months and 8 days
Pls help to convert it in months including the days
Thank you
Can you add a Now() function without it updating the time after every entry
i have that problem too. could you find any answer for it?
Wow
Hay the guy on the radio said that Y@K is coming back. I d better stock up.
nice. share the file
Link is in the description 👍
i found a short cut in the first 2 minutes !
Bonus!
I try your formulas but didnt work
First! :)
😂
Huh? The earliest possible date is January 1, 1900?? There are a lot of folks from the 19th Century and earlier that are rolling over in their graves right now.